DAX : Power BI Report Builder based on SSAS - If User selects value "ALL" from parameter then pass all values of the column else pass selected values

Question

I have a paginated report based on a SSAS tabular model source. It has a multi-select parameter called @Color.

The requirement is if the user selects one or more values from the @Color parameter dropdown and if the selection contains value "ALL" then all the values of the parameter column should be passed to the dataset ClientSales else if the selection does not contain "ALL" then only the selected values should be passed.

(I have UNIONed the value "ALL" to the dataset called ColorNames that is used in available values for @Color parameter)

Related Power BI file containing data and Paginated Report rdl can be accessed from below link (Please do consider changing the source in rdl by opening the PBIX file and copying the localhost Server Name using DAX Studio)

PBIX File RDL File

Any assistance would be highly appreciated.

[Update] I have been able to create 2 variables in the DAX Query, _dataset0 returns colors selected in slicer, _dataset1 returns ALL colors regardless of slicer selection

I need assistance with achieving the functionality using some conditional logic

Below is the DAX Query

DEFINE    
    var _dataset0 = // this dataset accepts multi-select values for Color
    SUMMARIZECOLUMNS(
     DimCLient[ClientName]
    ,DimGeography[CountryName]
    ,DimVehicle[Make]
    ,DimVehicle[Model]
    ,DimVehicle[Color]
    ,FILTER(VALUES(DimGeography[CountryName]), DimGeography[CountryName] = @CountryName)
    ,RSCustomDaxFilter(@Color,EqualToCondition,[DimVehicle].[Color],String)
    ,Sales, SUM(FactSales[SalePrice])
    )
    
    
    var _dataset1 = // this dataset returns all colors regardless of the selection in the slicer
    SUMMARIZECOLUMNS(
     DimCLient[ClientName]
    ,DimGeography[CountryName]
    ,DimVehicle[Make]
    ,DimVehicle[Model]
    ,DimVehicle[Color]
    ,FILTER(VALUES(DimGeography[CountryName]), DimGeography[CountryName] = @CountryName)
    ,KEEPFILTERS (
            FILTER ( ALL ( DimVehicle[Color] ), NOT ISBLANK (  DimVehicle[Color]  ) )
        )
    ,Sales, SUM(FactSales[SalePrice])
    )
    
    
    EVALUATE
    //trying to explore a possible solution using IF conditional logic
    //Like, IF colors selected in the slicer contains "ALL" THEN RETURN _dataset1 ELSE RETURN _dataset0
    
    _dataset1

Answer 1

I agree with @David Bacci, there is already the Select All Option (and like french people say on ne réinvente pas la roue). I understand that the UI/UX team is looking for something more friendly to the final user of the report. The solution I am providing may affect the performance even if it answers the need.

My solution impacts the model as I created a seperate table for the colors including the "All" : (using Power Query or importing it as a sheet in your source)

Then you need to have an extra column in your source where all the rows have "ALL", here is an example :

Then link your source to the colors table :

The active relationship :

The inactive relationship :

Now when it comes to the calculations/ measures, this is how to use the 2 relationships :

ConditionalSum = 
VAR SelectedColor = SELECTEDVALUE(Colors[Color], "All")
RETURN
IF(
    SelectedColor = "All",
    CALCULATE(
        SUM(datavehicles[CostPrice]),
        ALL(Colors), // Removes any filter from the Colors table.
        USERELATIONSHIP(datavehicles[All Colors], Colors[Color]) // This should be the active relationship.
    ),
    CALCULATE(
        SUM(datavehicles[CostPrice]),
        USERELATIONSHIP(datavehicles[Color], Colors[Color]), // This should be the inactive relationship.
        Colors[Color] = SelectedColor
    )
)

I used the ALL(Colors) to remove any filter from the entire Colors table when "All" is selected.