CODEWITHSUNDEEP
powerbi
cchp07
cchp07

Reputation: 19

Power BI If Blank Then Put Value

This snippet displays the chart we are looking at and it has blanks. Instead of blanks for those dates and ID's we want to show what those values are; however, the values(Rates) may not be the same for every ID or Date. (This may happen on rare occasions.)

Visual Display

This snippet displays the data being pulled into Power BI from excel (Just sample data). Notice that all the dates are in order, but notice that some of the ID's do not have entries for certain dates.

Table Display

I want to be able to say IF(ID is blank per this date, then put Value(Rate) that is attached to this ID).

This may not be possible to do in Power BI. We have an excel spreadsheet with similar data doing what we want but we wanted to automate by using Power BI.

Any Thoughts?

Update

I have got the measures to work in my favor, however; I need to get the total of the measure: Value Or Rate measure. I do have Value Or Rate - visual totals if I can do the same thing as the Value Or Rate measure with a total that would be great.

Please see the updated screen screenshots below.

Visual with fields Relationships

Upvotes: 0

Views: 28411

Answers (1)

greggyb
greggyb

Reputation: 3798

As a measure, you could do something like below. I'm assuming that you have a model as follows:

  • Fact: (Date, ID, Value)
  • DimDate: (Date, ...)
  • Rates: (ID, Rate)
  • Dim: (ID, ...)

With these tables you'd have relationships as below:

  • Dim -1:N-> Fact
  • Dim <-1:1-> Rates
  • DimDate -1:N-> Fact

With the model above, you could then build a visual of:

  • Rows: DimDate[Date]
  • Columns: Dim[ID]
  • Values: [Value Or Rate Measure]
Value = SUM ( 'Fact'[Value] )

Rate = SUM ( 'Rates'[Rate] )

Value Or Rate Measure =
VAR Value = [Value]
RETURN
  IF ( ISBLANK ( Value ), [Rate], Value )

This might not do what you want for totals - you didn't specify. So if you need visual totals you might try the following:

Value or Rate - visual totals =
SUMX (
  CROSSJOIN ( VALUES ( 'Dim'[ID] ), VALUES ( 'DimDate'[Date] ) ),
  [Value Or Rate Measure]
)

You could also handle this in Power Query M, assuming you have the same tables I've described above. I'm assuming each table has an associated query of the same name.

let
    Source = Table.AddColumn(Dim, "Date", each DimDate[Date]),
    #"Expanded Date" = Table.ExpandListColumn(Source, "Date"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Date", {"id", "Date"}, Fact, {"ID", "Date"}, "Fact", JoinKind.LeftOuter),
    #"Expanded Fact" = Table.ExpandTableColumn(#"Merged Queries", "Fact", {"Value"}, {"Value"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Fact", {"id"}, Rates, {"ID"}, "Rates", JoinKind.LeftOuter),
    #"Expanded Rates" = Table.ExpandTableColumn(#"Merged Queries1", "Rates", {"Rate"}, {"Rate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rates", "Value Or Rate", each if [Value] = null then [Rate] else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Rate"})
in
    #"Removed Columns"

Here, we're doing something similar, but at a table level, instead of as a measure. We crossjoin Dim[ID] and DimDate[Date] to get a dense table of all date and ID combinations. Then we left join the original Fact table and the Rates table. Then we add a column that takes [Value] if it exists, or [Rate] if [Value] is null.

Upvotes: 1

Related Questions