Forum Discussion

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago
Solved

DISTINCTCOUNT Measure in visualization

Hi, I have 3 different tables which is “Data”, Report” and “Status”. The “Data” and “Report” both tables are contain the same columns are “Fruits List, Area Code, Language and sales code” expect “i...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Saxon10 ,

     

    What you can do is to do a summarization with the calculation of the colours. For this what I have done is to create two other tables (in order to have the dynamic part of the filtering if you need).

     

    Colour

    Orange
    Green

     

    Formatting_Measure ID

    Fruit List 1
    Area Code 2
    Language 3
    Sales Code 4

     

    Now add the following measure:

     

    Count of Formats = 
VAR MEASURE_SELECTION =
    SWITCH (
        SELECTEDVALUE ( Formatting[ID] );
        1;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING FRUITLIST]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        2;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING AREACODE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        3;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING LANGUAGE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        4;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING SALESCODE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            )
    )
RETURN
    MEASURE_SELECTION

     

    See result below and in attach PBIX file:

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi Saxon10 ,

 

What you can do is to do a summarization with the calculation of the colours. For this what I have done is to create two other tables (in order to have the dynamic part of the filtering if you need).

 

Colour

Orange
Green

 

Formatting_Measure ID

Fruit List 1
Area Code 2
Language 3
Sales Code 4

 

Now add the following measure:

 

Count of Formats = 
VAR MEASURE_SELECTION =
    SWITCH (
        SELECTEDVALUE ( Formatting[ID] );
        1;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING FRUITLIST]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        2;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING AREACODE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        3;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING LANGUAGE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            );
        4;
            COUNTROWS (
                FILTER (
                    SUMMARIZE (
                        ALLSELECTED ( REPORT[ITEM] );
                        REPORT[ITEM];
                        "Format"; [FORMATTING SALESCODE]
                    );
                    [Format] = MAX ( Colours[Colour] )
                )
            )
    )
RETURN
    MEASURE_SELECTION

 

See result below and in attach PBIX file:

 

Saxon10's avatar
Saxon10
Post Prodigy
5 years ago

Thank you so much for your time and help. Your solution was amazing. This is exactly I am looking for it.