Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi guys,
I am a table of data similar to the one below:
ID | Assign to | Web Profile | Profile Attribute 1 | Profile Attribute 2 | More Attribute columns |
1 | A | A1 | Y | N | … |
1 | A | A2 | N | Y | |
2 | B | B | Y | N | |
3 | C | C | Y | Y | |
4 | D | D1 | Y | N | |
4 | E | D1 | N | N |
You will see for each customer ID, it can be assigned to different representatives - One ID can have several representatives to service at the same time like ID 4.
Meanwhile, eahc ID can have multiple web profile created for different purposes.
My goal of the exercise here is to add one CONDITIONAL COLUMN in my powberbi datatable to display the amount of unique/distinct web profile each ID has.
End result should looks like:
ID | Assign to | Web Profile | # Distinct Web Profile |
1 | A | A1 | 2 |
1 | A | A2 | 2 |
2 | B | B | 1 |
3 | C | C | 1 |
4 | D | D1 | 1 |
4 | E | D1 | 1 |
Any tips are appreciated.
Thank you!
Solved! Go to Solution.
Hi @ljx0648 ,
Ahmedx's solution is right , because you have multiple web Attributes, and you can use ALLEXCEPT to remove all other attribute column filters, and subsequently just filter the different rows of the assign to columns
Below is my table:
The following DAX might work for you:
Column_assign =
CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Assign to]))
The final output is shown in the following figure:
At this point in time, it would be difficult for you to compute this result with the rest of the variables in the property columns
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ljx0648 ,
Ahmedx's solution is right , because you have multiple web Attributes, and you can use ALLEXCEPT to remove all other attribute column filters, and subsequently just filter the different rows of the assign to columns
Below is my table:
The following DAX might work for you:
Column_assign =
CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Assign to]))
The final output is shown in the following figure:
At this point in time, it would be difficult for you to compute this result with the rest of the variables in the property columns
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for you quick response and time!
My appology here, I think I have supplied a over simplied sampe here, the datatbale looks like the one below:
ID | Assign to | Web Profile | Profile Attribute 1 | Profile Attribute 2 | More Attribute columns |
1 | A | A1 | Y | N | ... |
1 | A | A2 | N | Y | |
2 | B | B | Y | N | |
3 | C | C | Y | Y | |
4 | D | D1 | Y | N | |
4 | E | D1 | N | N |
|
May I know if there is anyway to count distinct only using the variables from ID & Web profile columns?
Because there are many different attribute columns after web profile & every one contains different info.
Thank you again for your time
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
105 | |
69 | |
49 | |
41 | |
34 |
User | Count |
---|---|
154 | |
112 | |
60 | |
54 | |
35 |