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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Chanleakna123
Post Prodigy
Post Prodigy

Count 13 Columns

HI All , I have W1 W2 until W13 , 

and i wanna Count W1 until W13 , 

it is like we count the Column, In Column 

as example below :

Customer Name BRAK KIMSY , Total Count : 4 

Customer Name PTEAS MAE RESTAURANT , Total Count 2 

Do you have any idea how to settle this ? I have explored for 4 hours , and no result. 

 

1.PNG

1 ACCEPTED SOLUTION

In theory, your code is correct. Is a measure or a calculated column?

 

Ok, the problem is custumer name has several rows, is that possible? Do you want a new table just a custumber name and count?

 

In the lookupvalue code, you need to add as many search  column name as columns you have:

 

Total Count = 
var _UserName= CountColumns[Customer Name]
var _Flavour= CountColumns[Flavour]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)

View solution in original post

3 REPLIES 3
ZunzunUOC
Resolver III
Resolver III

Hi @Chanleakna123 , maybe there are better solutions (a loop would be great), but this works, is a calculated column:

 

Total Count = 
var _UserName= CountColumns[Customer Name]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName)>0;1;0)

I have simplified with just 6 columns.

 

The result:

reply014.png

 

Best Regards,
Miguel

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

 

hi @ZunzunUOC 

there was a message pop up as per below. 

can you help me to acheive this ? 

this is my first try with 2 week only , and the message turn out as per below

1.PNG

In theory, your code is correct. Is a measure or a calculated column?

 

Ok, the problem is custumer name has several rows, is that possible? Do you want a new table just a custumber name and count?

 

In the lookupvalue code, you need to add as many search  column name as columns you have:

 

Total Count = 
var _UserName= CountColumns[Customer Name]
var _Flavour= CountColumns[Flavour]
return
IF(LOOKUPVALUE(CountColumns[W1];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W2];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W3];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W4];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W5];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)+IF(LOOKUPVALUE(CountColumns[W6];CountColumns[Customer Name];_UserName;CountColumns[Flavour];_Flavour)>0;1;0)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 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.

October NL Carousel

Fabric Community Update - October 2024

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