Microsoft PowerBI Match multiple values is seperate table using ID and Date

1
votes

I am making a demo IoT device monitoring dashboard. I can't understand which function should I use to check if devices are online on a certain date.

This is my sample report table1 from which I separate the Date to another column.

enter image description here

A device can report multiple times on a single day on the server. If it doesn't hit the server no report will generate.

Then I created a lookup table2 that contains all the device ID.

enter image description here

Now I created another table3 and generate a calendar date which I link with the table1 date.

Now In the column, I put my device ID and want to fill the column as true or false if the device reported on a particular date. I am unable to do it.

I used IF ( ISBLANK ( COUNTROWS ( RELATEDTABLE this function it didn't work

I want to create something like this. Which will look up the ID and date to report like it.

enter image description here

It will be a great help if anyone can share any idea.

2
powerbipowerbi-desktoppowerbi-custom-visuals
Do your final output shown is based on the sample data you shown? - mkRabbani
Is it ok if you get TRUE in respected cell but BLANK where FLASE? If this is ok with you, there is an easy solution. - mkRabbani
What is that? Share please - isteak007

2 Answers

1
votes

The screen shots below give you everything you need.

There is a Device Connections table of the devices and when they connected. I have converted the DateTime to a date so that it can be joined to the Dates table, just a list of dates you want to check connections for. I have a relationship that connect the dates of the two tables.

Note: You could also preserve the time at which the device was seen if needed. It is probably best to have it as a separate column. I have discarded the time for simplicity.

I have created a single measure:

 HasBeenSeen = IF (CALCULATE(COUNTROWS(DeviceConnections)) > 0, TRUE, FALSE)

Which gives a TRUE/FALSE if the device has been seen or not for whatever context exists (e.g. a given date). You could also just count the number of occurrences and display them.

Then I created a matrix visual with the Date from the dates table on the rows, Device ID on the columns and HasBeenSeen as the values to give the desired result.

enter image description here

enter image description here

enter image description here

1
votes

As I said in the comments to you question, if you accept BLANK in cells where you shown FALSE, you can apply this simple steps. Only Table1 is sufficient for this and need no other table or joining.

Create a very simple Measure like below.

true_false = "True"

Now add a Metrics and configure it as below-

enter image description here

And here is the final output-

enter image description here