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:

     

lbendlin's avatar
lbendlin
Super User
5 years ago

Power BI works better with a data model rather than with unconnected tables.  Try it out.

  • Saxon10's avatar
    Saxon10
    Post Prodigy
    5 years ago

    Hi,

    Thanks for your reply. Unable to open the attachment. Can you please check at your end.

    • 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.