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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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. 


@ 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!:
Mastering Power BI 2nd Edition

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]
  )

@ 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!:
Mastering Power BI 2nd Edition

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.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.