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
TT1
Helper I
Helper I

How to countif per columns to 1 unique Data

How do i summarize the title and result in a matrix and count it, in excel, it would be a simple countif.

 

Example, talbe below:

 

Week1Week2Week3
AAA
AAA
AAA
AAA
AAA
AAA
AAA
AAA
AAB
AAB
AAB
AAB
AAB
AAB
ABB
ABB
ABB
ABB
ABB
ABB
ABC

 

Need result to be:

 

 Week1Week2Week3
A21148
B 712
C  1

 

Thank you

2 ACCEPTED SOLUTIONS
tex628
Community Champion
Community Champion

You can just do a reference to the table and create a copy in power query. Then you can do all kinds of pivots without ruining your original table. 

The other solution i had in mind is creating a small table:

A

B
C

Then create a relationship between this column and each of your week columns. The relationships should not be active. 

Finally create measures, along this lines of this:

Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1))
Week2 = Calculate(countrows([Week2]) ; USERELATIONSHIP( [NewColumn] ; Week2))

etc...

Finally use the A/B/C column in the new table as a legend or row dimension with the new measures and you should be all set. 


I'm going on lunch but let me know if you need any help!

/ J

 


Connect on LinkedIn

View solution in original post

tex628
Community Champion
Community Champion

Alright, never mind my previous message. Heres the columns:

week1 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week1] = cat )
week2 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week2] = cat )
week3 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week3] = cat )


/ J


Connect on LinkedIn

View solution in original post

15 REPLIES 15
TT1
Helper I
Helper I

Thanks both, however, the data for A, B and C is done after the table have already been imported into powerbi.

 

Data gets imported, formula done in 'Data' to get the result of A, B and C. I cannot unpivot in power query if the data does not exist. I done it this way because A, B and C depends on other columns to get to that result.

tex628
Community Champion
Community Champion

The best way to solve this is to produce those column in power query if that is possible. Can you post the code you use to calculate the columns? 

If it's impossible there is a solution that i have in mind but it's not a nice one 🙂 

/ J


Connect on LinkedIn

Thanks for your help. To get the columns result, its just an IF formula. i.e. for week 1, IF (date in file opened is less than today(), result is A etc.

 

I can do it in power query but then wouldn't want to unpivot as am using other columns in table to produce results and graphs. I guess i can just duplicate the table and then add it in power query.

 

Would like to know your possible but not nice solution though 🙂

tex628
Community Champion
Community Champion

You can just do a reference to the table and create a copy in power query. Then you can do all kinds of pivots without ruining your original table. 

The other solution i had in mind is creating a small table:

A

B
C

Then create a relationship between this column and each of your week columns. The relationships should not be active. 

Finally create measures, along this lines of this:

Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1))
Week2 = Calculate(countrows([Week2]) ; USERELATIONSHIP( [NewColumn] ; Week2))

etc...

Finally use the A/B/C column in the new table as a legend or row dimension with the new measures and you should be all set. 


I'm going on lunch but let me know if you need any help!

/ J

 


Connect on LinkedIn

Worked great, thanks very much.

tex628
Community Champion
Community Champion

Great to hear, have a nice evening!

/ J


Connect on LinkedIn

Hi tex628


I now have another issue relating to the solution, didn't want to create another thread so thought i would ask in here.


In the new small table, for each category A, B & C, there are unit times next to it

 

CategoryUnit Time
A1
B2
C3

 

I have the calculated measure as per your solution so now all I want to do is multiply that measure week to the unit time. First one works fine when i put in (= unit time * week1) but then when i create another column for week 2 (= unit time * week2), it complains of a circular reference.

 

Would you know a solution to this?

 

Thanks

tex628
Community Champion
Community Champion

Just making sure im on the same page, 

This is what we have now, more or less:

Category Unit Time Week 1 Unit Time Week 2 Unit Time Week 3
A 1 4 7
B 2 5 8
C 3 6 9


And the measures [Week1], [Week2] & [Week3].

Generally thinking i believe this should work:
[Week1] * Table[Unit Time Week1]
[Week2] * Table[Unit Time Week2]
[Week3] * Table[Unit Time Week3]

Something along the lines of this:

Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1)) * SELECTEDVALUE(Table[Unit Time Week 1])

 

Let me know how it goes.

/ J




Connect on LinkedIn

Hi, i have attached the example below in onedrive

 

https://1drv.ms/u/s!AvR5HzU6KTFHjzB36pZthOULapqe?e=grVqcg

 

If you load it up and go to 'Data' and look at table 2, the week2 times is giving a cicular reference.

 

tex628
Community Champion
Community Champion

Alright, never mind my previous message. Heres the columns:

week1 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week1] = cat )
week2 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week2] = cat )
week3 times = 
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week3] = cat )


/ J


Connect on LinkedIn

Wow, amazing. Thanks very much

tex628
Community Champion
Community Champion

Implementing it in the matrix is quite simple:
image.png

Week1 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week1]))
Week2 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week2]))
Week3 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week3]))


On the other hand, producing the same numbers as calculated columns doesn't seem to be quite as easy. There's apparently an issue with creating several columns using different USERELATION() calculations. I'm going to be honest and say that i don't really know why this is the case.  

If it's necessary to get the values in calculated columns we might need to look at a different solution. But i'm going to take another look and see if i can get it to work. 

/J


Connect on LinkedIn

Thanks, will give this a try and report back, didn't know i could calculate via other relationships as i had a similar idea but couldn't work out how to do it.

tex628
Community Champion
Community Champion

Starting table:
image.png

Highlight all columns and press "Unpivot":
image.png

Then press "Group By":
image.png
Use these settings:
image.png

Finally highlight "Attribute" column and press "Pivot":
image.pngimage.png


Result:

image.png

/J


Connect on LinkedIn
amitchandak
Super User
Super User

@TT1 , unpivot this data and create week a dimension table and then you create this easily

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

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.