Displaying the non-tested values of a parameter based on some filters parameters, only in DAX

[Anonymous]

Hello,

I have a DataSource composed of 6 tables : Cart, AllApplesValues, AllOrangesValues, AllIsBioValues, AllNbWormsInsideValues, ParametersValues ( see below )

The Cart table is composed of data columns ( NbApples and NbOranges ) and filter columns ( IsBio and NbWormsInside ) like I can display the NbApples in a table visual and filter it with IsBio and NbWormsInside. The table looks like this : 

The AllApplesValues table is composed of a single column containing all the possible values that the NbApples column can have in the Cart table. The AllApplesValues table looks like this : 

The AllOrangesValues table is composed of a single column containing all the possible values that the NbOranges can have in the Cart table. The AllOrangesValues table looks like this : 

The table ParametersList is composed of a single column which contains the names of the differents parameters that I want to display in PowerBI. These parameters are the data columns in the Cart table. The ParametersList table looks like this :

The table AllIsBioValues is composed of a single column which contains all the possibles values of the column IsBio in the Cart table. The AllIsBioValues table looks like this :

The table AllNbWormsInsideValues is composed of a single column which contains all the possibles values of the column NbWormsInside in the Cart table. The AllNbWormsInsideValues table looks like this :

What I want to do with all these tables is, for example, to show the values of Apples from the table AllApplesValues that are not in the column NbApples in the table Cart and I can filter these data like depending of the selected values of IsBio and NbWormsInside in my report. Here's what it should look like and what I want the result to be : 

I can switch between NbApples and NbOranges so the content of the table ( the visual ) can change dynamically depending of the chosen paramter and the selected filtered values. I've made some research during a week but I can't find a way to do it

[v-yalanwu-msft]

Hi, @Anonymous ;

Yes, you could find it.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a flag measure as follow:

flag = 
SWITCH (
    MAX ( 'ParametersList'[Parameters] ),
    "NbApples",
        IF (
            MAX ( 'AllApplesValues'[NbApples] )
                IN SUMMARIZE (
                    FILTER (
                        'cart',
                        [IsBio]
                            IN ALLSELECTED ( 'AllIsBioValues'[IsBio] )
                                && [NbWormsInside] IN ALLSELECTED ( AllNbWormsInsideValues[NbWormsInside] )
                    ),
                    [NbApples]
                ),
            0,
            1
        ),
    "NbOranges",
        IF (
            MAX ( 'AllOrangesValues'[NbOranges] )
                IN SUMMARIZE (
                    FILTER (
                        'cart',
                        [IsBio]
                            IN ALLSELECTED ( 'AllIsBioValues'[IsBio] )
                                && [NbWormsInside] IN ALLSELECTED ( AllNbWormsInsideValues[NbWormsInside] )
                    ),
                    [NbOranges]
                ),
            0,
            1
        )
)

Then apply it into filter.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It works very well but is it possible to make sure that if I select NbOranges, the column of the table changes to NbOranges and not NbApples?