Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to Solution.
Hi @Anonymous ,
I haven't used TREATAS often, so had to figure out a way to meet your requirements. I like these type of questions because basically I learn a lot based on a real use case 🙂
Anyway, the solution comprises out of TREATAS, UNION and GENERATESERIES. The GENERATESERIES() function returns a one-column-table based on its parameters (start, end and interval(defaults to 1)) So, GENERATESERIES(1,4) will create a table like this:
1 |
2 |
3 |
4 |
UNION() takes two or more tables as input and appends them. So, if we use these together like this:
UNION(GENERATESERIES(1, 3), GENERATESERIES(8, 10))
The resulting returned table is:
1 |
2 |
3 |
8 |
9 |
10 |
Now, combining this in your case, leads to the following DAX:
Test = CALCULATE(
[Finance];
TREATAS(
UNION(GENERATESERIES(1071; 1116); GENERATESERIES(1000; 1005); GENERATESERIES(1119; 1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102; 9999); GENERATESERIES(1062; 1066); GENERATESERIES(1007; 1060);
Purpose22[Purpose Code])
))
Let me know if this solves your issue 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @Anonymous ,
I haven't used TREATAS often, so had to figure out a way to meet your requirements. I like these type of questions because basically I learn a lot based on a real use case 🙂
Anyway, the solution comprises out of TREATAS, UNION and GENERATESERIES. The GENERATESERIES() function returns a one-column-table based on its parameters (start, end and interval(defaults to 1)) So, GENERATESERIES(1,4) will create a table like this:
1 |
2 |
3 |
4 |
UNION() takes two or more tables as input and appends them. So, if we use these together like this:
UNION(GENERATESERIES(1, 3), GENERATESERIES(8, 10))
The resulting returned table is:
1 |
2 |
3 |
8 |
9 |
10 |
Now, combining this in your case, leads to the following DAX:
Test = CALCULATE(
[Finance];
TREATAS(
UNION(GENERATESERIES(1071; 1116); GENERATESERIES(1000; 1005); GENERATESERIES(1119; 1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102; 9999); GENERATESERIES(1062; 1066); GENERATESERIES(1007; 1060);
Purpose22[Purpose Code])
))
Let me know if this solves your issue 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @JarroVGIT thanks for the suggestion, but my code does noet return any values.
Do you see what i am missing? 🙂
TEST= CALCULATE(
[Finance Actual Amount];
TREATAS(
UNION(GENERATESERIES(1071;1116;1071); GENERATESERIES(1000;1005); GENERATESERIES(1119;1258); GENERATESERIES(1260 ;2098); GENERATESERIES(2102;9999); GENERATESERIES(1062;1066); GENERATESERIES(1007;1060));Purpose[Purpose Code]))
Kind regards Søren
I re-ran my test to double check if what I said was true, but I should've looked closer at your DAX 😛
The first GENERATESERIES has 3 arguments. Else this should be working or there is another filter active (slicer?)
I retested it with this dataset:
Name | ID
A | 1 |
B | 2 |
C | 3 |
D | 4 |
E | 5 |
F | 6 |
G | 7 |
H | 8 |
I | 9 |
J | 10 |
with measure:
Measure 2 = CALCULATE(DISTINCTCOUNT(Table2[Name]), TREATAS(UNION(GENERATESERIES(1,4), GENERATESERIES(8,10)), Table2[ID]))
This returns 7 as I expect it would.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |