The values within the table should be 0 , if no data found in table rather than having a blank table

[Anonymous]

Hello Folks,

Please find the pbix file in the link given below

https://www.dropbox.com/s/j4y305smqj8geji/Sample_File.pbix?dl=0

I need your urgent help for the given scenario.

I have a table visual in powerBI where all the values reflect if there are values found in count_record and count_transaction measure as given below:

User	Client	Appliance	Count_Record	Count_Transaction
John smith	Orange	Sony	1000	1000
John Smith	Orange	Microsoft	1400	1300
John Smith	Orange	Panasonic	2000	0
Paul Logan	Orange	Samsung	1500	1500
Paul Logan	Orange	Sony	1800	1800
David Clarke	Kite	LG	0	2000
David Clarke	Kite	Microsoft	2500	2500

But as soon as I change the date or the appliance values from the slicer, the whole table goes blank because there is no value found.
What I want is something like this, if there is no data found for these 2 measure, this is how the table should look. all the values should be there with 0 in count_record and count_transaction. The values should only change when I change the client from the client slicer or Appliance slicer. For Example: If we select orange from the client slicer then I should get all the users in the rows along with only those appliances that they have used in the past with 0 values in count_record and count_transaction, if no data is found.

User_Name	Client	Appliance	Count_Record	Count_Transaction
John smith	Orange	Sony	0	0
John Smith	Orange	Microsoft	0	0
John Smith	Orange	Panasonic	0	0
Paul Logan	Orange	Samsung	0	0
Paul Logan	Orange	Sony	0	0

I tried these measure but that didn't work

Final_Count_Record = IF(ISBLANK(Count_Record),0,(Count_Record))

Final_Count_Transaction = IF(ISBLANK(Count_Transaction),0,(Count_Transaction))

[Anonymous]

Hello @AllisonKennedy 

This logic actually worked for me. Exactly what I needed.
Highly Appreciated and Thankful for explaining the logic on pbix file.

Best Regards,

Ashish Oza

[AllisonKennedy · Accepted solution]

@Anonymous  A few notes for you: 

1) You don't need the inactive relationship between Client and Transaction/Record tables.

2) Use a DateKey for your date relationships (one is text and one is date, so that's not currently working): https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

3) Clear the date filter in your measure and use that to decide whether to add 0 to the Record/Transaction Count. Here is the formula for Record count:

Count_Record =

VAR _CountRecordSelectedDate = CALCULATE(DISTINCTCOUNT(Record[Record_Id]))

VAR _CountRecordAllDates = CALCULATE(DISTINCTCOUNT(Record[Record_Id]), ALL('Calendar'))

RETURN

IF( _CountRecordAllDates > 0, _CountRecordSelectedDate +0, _CountRecordSelectedDate)

4) and for Transaction:

Count_Transaction =

VAR _countAlldates = CALCULATE(DISTINCTCOUNT('Transaction'[Transaction_Id]),ALL('Calendar'))

VAR _countselecteddates = DISTINCTCOUNT('Transaction'[Transaction_Id])

RETURN

IF(_countAlldates>0, _countselecteddates+0, _countselecteddates)

Your updated sample file is below my signature.