topic Re: Creating a measure based on condition in DAX Commands and Tips

Creating a measure based on condition

Anonymous — Wed, 02 Sep 2020 08:20:03 GMT

Hi All,

I am trying to create a measure that determines the number of items are at 0%. To do that, this is the step that i have undertaken

1. Create a measure to determine the distinct count of the items of interest. Formula used : DistinctCountNoBlank(Table[Reference_Col])

2. Then i separately calculated another measure that calculate the status to see if its 0%

3. Then i created a summary table using the summarize function to tabulate out the reference item and the status column and do a count row for those row that return a status of 0%.. However, the answer is not what i expected.

How do i create a measure that can give me the number without creating a summary table?

Thanks in advance !

Re: Creating a measure based on condition

Pragati11 — Wed, 02 Sep 2020 08:48:15 GMT

Hi Anonymous ,

Do share some sample screenshots please of the data and desired output.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Thanks,

Pragati

Re: Creating a measure based on condition

amitchandak — Wed, 02 Sep 2020 09:31:15 GMT

Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Creating a measure based on condition

Anonymous — Wed, 02 Sep 2020 09:35:24 GMT

This is not enough information to let anyone tell you what you should do. Please add more detailed info following this post as a guide:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Creating a measure based on condition

Anonymous — Wed, 02 Sep 2020 14:03:37 GMT

Pragati11

Many thanks for the response. Below pic shows the simplified table to explain what i am trying to achieve:

For the top table, it represents a simplistic table of my data source. I have created 3 measure to determine the distinct count of the number of equipment and the percentage of working equipment. I also wanted to create a measure to calculate number of items that have the % of working equipment status at 0% but i am unsure how to write that in DAX. Therefore, I use the table visual in powerBI to return the results (as shown like in the table below) with the column Description containing the unique values of the list of items.

However, since I need a measure to have the count of the number of items that has status of working equipment at 0%, i went on to create a summary table (summarized based on description of items and with column on the number of equipment and the status of working equipment using the measures that i have created earlier) so that i can count the number of rows of items that has 0% using CountA function. But the results of both table does not tally. Not sure what went wrong here.