Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mhsk
Helper II
Helper II

Show Measure for missing data point in dax

Hi,

 

I have a fact table with data but not for all data points in calendar table, when i trow data into matrix it shows blanks when there is no corresponding value - which is ok, but i'm trying to replace blank value with my measure, please see example:

 

mhsk_0-1659423192042.png

 

when i pass a measure:

 

ReplaceBlank = IF( ISBLANK(MyOtherCalc), MyCalc, MyOtherCalc )

 

it does not return any value. If i pass a measure: ReplaceBlank2 = IF( ISBLANK(MyOtherCalc), 0, MyOtherCalc ) it works fine and return 0 when there is a blank value in matrix:

mhsk_1-1659423409893.png

 

How would i pass a measure instead of 0 in fields i do not have a data values (blanks) ?

 

@Greg_Deckler suggested to use isblank in othe post to receive 0 but it seems thats not a solution in my case.

6 REPLIES 6
mhsk
Helper II
Helper II

Thanks for promp reply @Greg_Deckler , you just realized me my measure is not returning any value as there is no value for given calendar table week so nothing to return, now i made it work with simple allexept but i see my problem is more complex:

 

I found now that i need to reference to previus calculated measure value, not to constant value when there is no value to show from fact table, would you advise how to build such measure (that will produce the same value that was present in previously (previous date) ? 

 

so what i'm receiving now is this:

mhsk_2-1659444488252.png

 

and i'm expecting this:

mhsk_1-1659444367731.png

 

many thanks for all your suggestions - not only in this topic, they save lives! (and a lot of time)

 

@mhsk So, what you will need to do is to check if the value returns blank and you will need a table VAR that returns values for each date. Then you can get the MAX (MAXX) Date where that value is not blank and use the value for that date. I don't really know the specifics of your source data so it's hard to be specific. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler  yes this is the logic i would need to pass to get the result. Check if blank its easy with ISBLANK but i can overcome creating virtual table that will check what is the last non blank value/date and copy that to any blank field until new value is present in the fact table. Would you suggest any solution? .pbix file is here https://we.tl/t-fgPJcyZ3ci

@Greg_Deckler  any chance you could have a look at the pbix?

what you have written: "you will need a table VAR that returns values for each date." doesnt show up in any scenario which approach shall i take? any suggestion appreciated

@mhsk So, the concept would be this:

New Measure = 
  VAR __Table = SUMMARIZE('Table',[Date],"__Value",[Your measure])
RETURN
  IF(ISBLANK([Your measure]),
      VAR __Date = MAXX(FILTER(__Table,[__Value] <> BLANK(),[Date])
    RETURN
      MAXX(FILTER(__Table,[Date] = __Date),[__Value]),
    [Your measure]
  )

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@mhsk I can only speculate that your other measure is also returning blank. You can confirm this by creating a measure ReturnZero = 0 and then this:

ReplaceBlank = IFISBLANK(MyOtherCalc), ReturnZero, MyOtherCalc )

 

That should also return 0.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.