I would like to create a formula for a new column named SETTING that will either say Error, Missing, or Refresh depending on the conditions met.
For the table below, there are two rows for each device STATUS. One "In Service" and the other "Archive".
I would like to create a formula that will display "Missing" if the DATE is blank for both "In Service" and "Archived" for the same device.
For example, the table bellow, the DATE column is blank for both rows. I would like SETTING column to display "Missing" for X1.
For the next example, the device Archived has a value in the DATE column but not in the In Service column then it should display Error.
Lastly, if both In Service and Archived have Dates then the column should display Refresh
Is this possible in Power Bi? I'm very new to this program.
Solved! Go to Solution.
try to add a column with this:
try to add a column with this:
Wow thank you so much.
Would this work if the table is formed from a relationship of other tables? I formed a relationship between three tables to get the data. Not only that, the storage mode is direct query from different sql databases.
When I create column using code I get a warning that measure formula refers to a column that contains many values...
how are the tables related?
It gives a warning, because the row context provided by FILTER does propagate to other tables. Some RELATED/RELATEDTABLE might be needed, if your model support the expected calculations.
oh I think I'm understnading. In this case cymrecloser is related to pb_equipment with DEVICE many to 1 and pb_equipment is related to mapping_view_recloser thru global_id many to 1. I'll play around with this to see if I can get it to work.
This calculated column formula works
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[DEVICE]=EARLIER(Data[DEVICE])&&Data[DATE]<>BLANK()))=1,"Error",if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[DEVICE]=EARLIER(Data[DEVICE])&&Data[DATE]<>BLANK()))=2,"Refresh","Missing"))
Hope this helps.
Brute force-ish (someone may come up with a much more elegant way to iterate through multiple primary key references by using a unique string)..
Long way (split data into 2 tables):
1) duplicate your data source in transformation
2) in 1 table filter status column to Archived, in the other filter to In Serivce (splits out different data, but keeps primary key).
3) Make sure date data type is set to Date.
4) Change names of tables to Archive/In Service so you know which ones are different.
In the In Service Table I created a custom column:
Setting = IF ( AND ( ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) ), ISBLANK ( 'In Service'[Date] ) ), "Missing", IF ( AND ( NOT ( ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) ) ), ISBLANK ( 'In Service'[Date] ) ), "Error", IF ( AND ( NOT ( ISBLANK ( LOOKUPVALUE ( Archive[Date], Archive[Device], 'In Service'[Device] ) ) ), NOT ( ISBLANK ( 'In Service'[Date] ) ) ), "Refresh", "No Condition Set" ) ) )
Output is below
Get a sneak peek into this year's Power Platform Conference Keynote.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!