Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I'm trying to archive the below, but I have no idea what DAX to use. Is anyone able to help?
I am trying to calculate a table by counting how many entries have a certain attribute.
This is what Table A looks like
Time A | Time B |
3 | 4 |
6 | 1 |
2 | 6 |
3 | 4 |
This is what the end result should look like
V | Time A | Time B |
1 | 0 | 1 |
2 | 1 | 0 |
3 | 2 | 0 |
4 | 0 | 2 |
5 | 0 | 0 |
6 | 1 | 1 |
Thanks in advance,
JT
Solved! Go to Solution.
I Solved it!
I created a new Table unsing excel with the numbers 1 to 24
I then calculated new rows using:
Time A =
IF(
LOOKUPVALUE(TableA[TimeA], TableA[TimeA], TableB[V])
=TableB[V],
CALCULATE(
COUNT(TableA[TimeA]),
FILTER(TableA, TableA[TimeA] = TableB[V])
),
0)
I Solved it!
I created a new Table unsing excel with the numbers 1 to 24
I then calculated new rows using:
Time A =
IF(
LOOKUPVALUE(TableA[TimeA], TableA[TimeA], TableB[V])
=TableB[V],
CALCULATE(
COUNT(TableA[TimeA]),
FILTER(TableA, TableA[TimeA] = TableB[V])
),
0)
If the real data/model are close to the model provided it should work the same. If they are materially different you'll have to repost with more information.
I suggest you go through my suggested solution with the test data and try to make it work. I won't do it for you (that's not my style) but I will help you if you put in the effort.
1st step. create a numbers table (search online for powerbi numbers table). It won't be with an IF statement.
---
"But how do assign the results to Table B?" Table B is the matrix visual, so follow the instructions. I will help if you get stuck
You can do this by creating a 'numbers' table which you could hardcode e.g. 1->6 or create dynamically. I'll let you investigate how to do that.
You could unpivot Table A and then create a relationship between the numbers table and the unpivoted Table A.
Create a measure which will be a simple count of the Values . If you put (+ 0) in the measure that will help later.
Now create a matrix visual with the number from the numbers table, attribute from unpivotted Table A and the measure in the Values section.
Well that's a description of what to do.
Let me know how you get on.
First: thanks for fast help.
I guess I did not quite explain myself all to well.
The original Table A as over 15K entries.
I want to calculate Table B (the final result) to use it for a bar-graph.
Is the path described by you still viable? By hardcoding, do you mean using a stacked IF-Function? But how do assign the results to Table B? Sorry for asking such noob questions, but hours of youtube and searching the forum didnt produce any results.
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |