Replace the values with 0, if no data found in table rather than having a blank table

[Anonymous]

Hello Folks,

I need your urgent help for the given scenario.

I have a given 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 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 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

FYI, these are how the measures are created and tables are joined

Count_Record = Calculate(distinctcount(Record_Table[record]_id),USERELATIONSHIP(Client_Table[Client_Id], Record_Table[Client_Id]))

Count_Transaction = Calculate(distinctcount(Transaction_Table[transaction_id]),USERELATIONSHIP(Client_Table[Client_Id], Record_Table[Client_Id]))

Tables information is given below:

Count_Table & Transaction_Table is connected to User Table on User_id 

Record_Table has the user_id, client_id, Appliance, Record_Id, Date.

Transaction Table has user_id, client_id, Appliance, Transaction_Id, Date

User table has Client_id, user_Id & User_Name

Client_Table has Client_Id & Client_Name

Date Table has date (dd/mm/yyyy) and connected to record and transaction table

Appliance table has appliance values( Sony, Microsoft, LG, Samsung) connected to record and transaction table.

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

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please check the permission of your shared sample data. Please also try the following measure:

Count_Record = Calculate(distinctcount(Record_Table[record]_id),USERELATIONSHIP(Client_Table[Client_Id], Record_Table[Client_Id]))+0


Count_Transaction = Calculate(distinctcount(Transaction_Table[transaction_id]),USERELATIONSHIP(Client_Table[Client_Id], Record_Table[Client_Id]))+0

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[mohammedadnant]

Hi @Anonymous 

I tried with a sample data, it is working fine as below image

Transaction table connected with Region Master

TransDate from Transaction table 

RegionName from Region Master

Sales is direct column in top table

Sales is a measure in bottom table

fyi, both the measures are giving the same result as below table

Thanks & Regards,

Mohammed Adnan

learn Power Platform from my channel TAIK18 <-- click on the name

[amitchandak]

@Anonymous , This should have worked

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

Final_Count_Record = [Count_Record] +0

or try this option

if not

Can you share a sample pbix after removing sensitive data.

[Anonymous]

Hello @amitchandak 

Thanks for your kind help.
Please find the file the link given below:

https://drive.google.com/file/d/1UdWF6o6h60nVh9Uorrvjw6XeODgI0y9_/view?usp=sharing

[Anonymous]

Hello @amitchandak 

Thanks for your response.
I will upload the file in few mins