DISTINCTCOUNT Measure in visualization

[Saxon10]

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

[lbendlin]

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

[Saxon10]

Hi,

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

[MFelix · Accepted solution]

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]

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

[lbendlin]

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]

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]

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]

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.