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.
Dear Friends,
i have fact and dimension tables with more than thousands of rows as shown:
i want to get Status data in dimension table from fact table in which MR have only Status="Initial" else empty
for example in fact table: 1317328 & 1321621 have multiple rows but only status="Initial" so the output for dimension table like be:
Could you please help me, how can i achieve this
Thanks
Solved! Go to Solution.
@Anonymous , Try like
New column =
var _cnt = countx(filter(fact, Fact[MR Number] = dim[MR Number] && fact[Status ] ="Initial"), Fact[MR Number] )
var _cnt1 = countx(filter(fact, Fact[MR Number] = dim[MR Number] ), Fact[MR Number] )
return
if(not(isblank(_cnt)) && _cnt = _cnt1, "Initial", blank())
@Anonymous
I saw you have different "Initials", you need to unify the letter. maybe in PQ, capitalize each word.
Then create a column in table2
Column =
if (NOT(ISBLANK(MAXX(FILTER('Table','Table'[MR]='Table (2)'[MR NUMBER] &&'Table'[Capitalize Each Word]<>"Initial"),'Table'[MR]))),BLANK(),"Initial")
Proud to be a Super User!
@Anonymous a, new column in dimension table
New column =
var _cnt = countx(filter(fact, Fact[MR Number] = dim[MR Number] && fact[Status ] ="Initial"), Fact[MR Number] )
return
if(isblank(_cnt), blank(), "Initial")
Dear Sir
i tried with your dax but MR Number are also appearing where Status="Initial" or "Other value".
Output with your dax:
Actually i am looking for those MR which have only Status="Initial"
Requiement Output like:
Thanks
@Anonymous , Try like
New column =
var _cnt = countx(filter(fact, Fact[MR Number] = dim[MR Number] && fact[Status ] ="Initial"), Fact[MR Number] )
var _cnt1 = countx(filter(fact, Fact[MR Number] = dim[MR Number] ), Fact[MR Number] )
return
if(not(isblank(_cnt)) && _cnt = _cnt1, "Initial", blank())
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 |
---|---|
106 | |
104 | |
78 | |
68 | |
61 |
User | Count |
---|---|
144 | |
106 | |
106 | |
82 | |
70 |