Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost 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 “item” column in “Report” tables.

The “Status” table contain the Comments which is “Orange” and “Red”.

I made a conditional formatting based on the conditional formatting comments in visualization with Data and Report tables.

I am trying to achieve DISTINCTCOUNT in visualization based on the conditional formating comments in visualization data and status Table.

Herewith attached the file for your reference https://www.dropbox.com/s/us8y0ik5bczsl6h/COUNT%20VISULA1.pbix?dl=0

 

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:

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    What's your reason for not linking the Data and Report tables via the ID column (the composite key)?  Using LOOKUPVALUE() is not really encouraged. I assume this is something you did in Excel first?

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      No I didn't make any data in Excel. The way I matched inbetween two tables, You can see the conditional formating measure and conditional formating comments measure on the file.The data table more general but the report table on item level that the reason you unable to find the link in between.

       

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    DISTINCTCOUNT() requires you to specify a column or group of columns (you can also use VALUES() for that same goal).  

     

    When you have two orange values in the Sales Code column (for example) then the resulting DISTiNCTCOUNT() will be 1, not 2.  Maybe you want the COUNTROWS() for each color?

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      thanks for your reply. Can you please help me how can I apply both function in measue? Can you please advise and attach your working file as well.

       

      I am trying to count each columns here. Each column contiain two status which is red and green. I am trying get the count according to the color code against the columns based on the visual data,,,,Fruist list, Area code,Lanuage and sales code.

       

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

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

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost 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
        Icon for Super User rankSuper 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: