Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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())