Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Using the same measure for different columns of a table

Hello, I have a data table that contains information about testing produced items. After an item is produced, it needs to go through several test phases. An item being in a phase means it has ben qualified at the previous phases, so if [Current phase] = 3, it means it is qualified at phases 1 and 2.  My data table looks like this:

 

IDProduction datestatusCurrent phasePhase 1Phase 2Phase 3Phase 4Phase 5
11-1-2023qualified2qualifiedwaiting for results   
22-1-2023qualified2qualifiedwaiting for results   
33-1-2023qualified3qualifiedqualifiedwaiting for results  
44-1-2023qualified2qualifiedwaiting for results   
55-1-2023qualified2qualifiedwaiting for results   
66-1-2023qualified3qualifiedqualifiedwaiting for results  
77-1-2023disqualified4qualifiedqualifiedqualifieddisqualified 
88-1-2023qualified3qualifiedqualifiedwaiting for results  
99-1-2023qualified3qualifiedqualifiedwaiting for results  
1010-1-2023qualified5qualifiedqualifiedqualifiedqualifiedqualified
1111-1-2023qualified2qualifiedwaiting for results   
1212-1-2023qualified3qualifiedqualifiedwaiting for results  
1313-1-2023disqualified2qualifieddisqualified   
1414-1-2023qualified3qualifiedqualifiedwaiting for results  
1515-1-2023disqualified1disqualified    
1616-1-2023qualified5qualifiedqualifiedqualifiedqualifiedqualified
1717-1-2023qualified3qualifiedqualifiedwaiting for results  
1818-1-2023qualified3qualifiedqualifiedwaiting for results  
1919-1-2023qualified2qualifiedwaiting for results   
2020-1-2023qualified4qualifiedqualifiedqualifiedwaiting for results 
2121-1-2023qualified5qualifiedqualifiedqualifiedqualifiedwaiting for results
2222-1-2023disqualified2qualifieddisqualified   
2323-1-2023qualified3qualifiedqualifiedwaiting for results  
2424-1-2023qualified5qualifiedqualifiedqualifiedqualifiedqualified
2525-1-2023qualified4qualifiedqualifiedqualifiedwaiting for results 
2626-1-2023disqualified2qualifieddisqualified   
2727-1-2023disqualified1disqualified    
2828-1-2023disqualified2qualifieddisqualified   
2929-1-2023disqualified2qualifieddisqualified   
3030-1-2023disqualified5qualifiedqualifiedqualifiedqualifieddisqualified

 

To analyze this, I want to calculate the percentage qualified at each phase. My resulting table should look like this:

 Phase 1Phase 2Phase 3Phase 4Phase 5
qualified2817853
disqualified25011
waiting for results06921
Percentage qualified93%61%47%63%60%

 

I have created a measure in two different ways to calculate the number of qualified items of the first phase:

# qualified phase 1 = CALCULATEDISTINCTCOUNT('Table'[id]), FILTER('Table', [Current phase] > 1))
# qualified phase 1 =
CALCULATE(DISTINCTCOUNT('Table'[id]), FILTER('Table)', 'Table'[Phase 1] = "qualified"))


These measures both work, however, I would need to create separate measures for each of the phases, and in reality, there are numerous more phases than displayed in this example of the data. I have tried to create a new table with these success rates, but this won't allow me to drill down the data by dates. 

Any ideas on how to create one measure for each of the phases or any other suggestions would be very much appreciated!

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

In Power Query, select the first four columns, and then select the Unpivot Other Columns:

vzhangti_0-1700640383746.png

Result:

vzhangti_1-1700640498403.png

Measure:

Measure = 
Var _count1=DISTINCTCOUNT('Table'[ID])
Var _countall=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Value]<>BLANK()))+0
Var _countqualified=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Attribute]=SELECTEDVALUE('Table'[Attribute])&&[Value]="qualified"))
Return
IF(HASONEVALUE('Table'[Value]),_count1,DIVIDE(_countqualified,_countall))

vzhangti_2-1700641233579.png

Is this the result you expect? Please see the attached document.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Anonymous 

 

You can try the following methods.

In Power Query, select the first four columns, and then select the Unpivot Other Columns:

vzhangti_0-1700640383746.png

Result:

vzhangti_1-1700640498403.png

Measure:

Measure = 
Var _count1=DISTINCTCOUNT('Table'[ID])
Var _countall=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Attribute]),[Value]<>BLANK()))+0
Var _countqualified=CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),[Attribute]=SELECTEDVALUE('Table'[Attribute])&&[Value]="qualified"))
Return
IF(HASONEVALUE('Table'[Value]),_count1,DIVIDE(_countqualified,_countall))

vzhangti_2-1700641233579.png

Is this the result you expect? Please see the attached document.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Rupak_bi
Responsive Resident
Responsive Resident

I think summarizetable will solve the purpose

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.