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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need help comparing rows and after that counting

Hello everyone,

I have a dataset a little more complicated but in the same direction as this:

I can arrange in 2 types:
Type1:

SubjectName1State1Name2State2Name3State3Name4State4Output
AABC1ABC2EFG7PRO51
BEFG4ADC7ABD2ABD31
CABD2ABD3ABD4PRO22

 

Type2:

SubjectNameStateOutput
AABC21 - same output as the other for this subject
AABC11 - same output as the other for this subject
AEFG71 - same output as the other for this subject
APRO51 - same output as the other for this subject
CABD22 - same output as the other for this subject
CABD32 - same output as the other for this subject
CABD42 - same output as the other for this subject
CPRO22 - same output as the other for this subject

Name needs to be in the approved state 2 or 3, and not to count if it is not repeated in another column like in C Subject ABD appears 2 times with the States 2 & 3 but is counted one single time.

I have managed to do it using measure with the Type2 table arrange but i need it to be in column : 

CALCULATE(DISTINCTCOUNT('File'[Name]),'File'[State] in {"2","3"}) .

Note that I can not use the measure in a table that I will dispaly the subjects with all of the names and the states cause it will split and count every row in part.
 
Do you have a solution for me? Thank you!
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I managed to solve my problem using the method of the accepted solution in here . Just added one more filtering to the calculate.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

I managed to solve my problem using the method of the accepted solution in here . Just added one more filtering to the calculate.

NikhilChenna
Continued Contributor
Continued Contributor

Hi @Anonymous ,

It is bit confusing to undertand.

Can you paste your desired output according to the Type 1 and Type 2 table.

Waiting for you resposne @Anonymous .

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Anonymous
Not applicable

Hi @NikhilChenna ,
Sorry for the confusion.

The fact is that I want the same output. I can have the table in 2 different types as I shown.
Let's take the second. 
For Subject 1 I want the output 1 cause it needs to count only one name within the state 2 or 3. Only the first name ABC has the valid state for me . The rest of them have invalid states for me (not 2 or 3).


If we look into the C subject it has 4 names and there it need to count 2 because even if I have 1 name that appears 2 or multiple times with valid state (2 or 3), it need to count only 1 single time that name. So ABD-2 ABD-3 ABD-4 counts 1 time and PRO-2 counts 1 time so COUNT=2 (Out of 4 names, 3 have a valid state but one name with valid state that has the same value with another one with different or even same state need to count 1 not 2)

 

Hope it is more clear!

 

Hi @Anonymous ,

If you are looking for a output like this below, then follow the below steps.

NikhilChenna_0-1669728776986.png

1. you have to make a calculated column in the table , using the below dax.

Check = IF('Table (2)'[State]=2 || 'Table (2)'[State]=3,1,0)
 
2. After that you have to create a new table from your table, go to report view, click on the modelling tab from the above ribbion, click on the new table icon. after that use the below dax to create a summarize table.
 
Table 2 =
SUMMARIZE(
    'Table (2)',
    'Table (2)'[Subject],'Table (2)'[Name],
    "MAX check",MAX('Table (2)'[Check])
)
 
Which will give you the output as shown in the above screen shot.
 
Regards,
Nikhil Chenna
 
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Hi @NikhilChenna ,

Not really. I want the output to be like this 

AndreiSfarc_0-1669733546575.png

To have in the column output the count for the whole Subject

 

Hi @Anonymous,

 

Can you tell me the condition for the Output column.

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Anonymous
Not applicable

As I told you earlier, the ouput column counts within a subject, how many name in the states of 2 or 3 are. Example : I have subject A with ABC-1 ABC-2 ABC-3 FEG-2 FEG-3 PRO-3 PRO-5. THe output column will count ABC one time, FEG one time & PRo 1 time, some the ouput will be 3 . Explenation is: It counts ABC one time cause even there are 2 ABC in my valid states, because it is the same name I want only 1 count not 2., same fot the others.

AndreiSfarc_0-1669734328038.png

 

Hi @Anonymous , 

As per my previous solution it will work , 

 

Type2:

SubjectNameStateOutput
AABC21 - same output as the other for this subject
AABC11 - same output as the other for this subject
AEFG71 - same output as the other for this subject
APRO51 - same output as the other for this subject
CABD22 - same output as the other for this subject
CABD32 - same output as the other for this subject
CABD42 - same output as the other for this subject
CPRO22 - same output as the other for this subject

 

So by this you wanted to calculate the count of ABC as 1, ABD as 1 and PRO as 1 as they are in 2 and 3 state. So below screenshot shows that only.

 

If you are looking for a output like this below, then follow the below steps.

NikhilChenna_0-1669734508028.png

 

1. you have to make a calculated column in the table , using the below dax.

Check = IF('Table (2)'[State]=2 || 'Table (2)'[State]=3,1,0)
 
2. After that you have to create a new table from your table, go to report view, click on the modelling tab from the above ribbion, click on the new table icon. after that use the below dax to create a summarize table.
 
Table 2 =
SUMMARIZE(
    'Table (2)',
    'Table (2)'[Subject],'Table (2)'[Name],
    "MAX check",MAX('Table (2)'[Check])
)
 
Which will give you the output as shown in the above screen shot.
 
Regards,
Nikhil Chenna
 
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

 

Anonymous
Not applicable

So, I think I am not making myself very clear. I want the total count for that subkect to be displayed near it. Not only 1 or 0 cause it does not help me. This is a previous step of want I want to do further, for a filtering for Single or Multiple (Single= only one distinct name in the 2 or 3 status, Multiple= more than 1 distinct names in status 2 or 3)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors