Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
i am using direct query, 2 columns product service, product status and below is sample data
product service, product status
phase 1 open
phase 2 started
phase 3 waiting
phase 4 comlpleted
when i use this data into matrix i got below view, assume there are 30 open rows for phase 1,20 started rows for phase 2, 10 waiting rows for phase 3
and 05 completed rows for phase 4 then my matrix shows like below
product statusop open, started, waiting , comlpleted
product service
phase1 30
phase 2 25
phase 3 20
phase 4 05
in above matrix view i am getting empty values when there is no prodcuts compare with other phases, here i would like to show 0
when there is no prodcuts assigned or there is no data (null or blank)
in matrix rows are product service
columns are product status
values are count(product service)
my expected output like below
product status open started waiting comlpleted
product service
phase 1 30 0 0 0
phase 2 0 25 0 0
phase 3 0 0 10 0
phase 4 0 0 0 0
phase 4 0 0 0 05
i have tried with checking by isblank() like below
emptystatus= var test=calculate(count(table[product status])) return if(emptystatus= blank(),0,emptystatus)
and tried with diffrent options but still i am unable to show 0 where there is no data associated when the data is transorm into matrix.
Please look into this and provide your inputs. Thank you
Thank you
Mahi1827
Solved! Go to Solution.
This can e achieved by adding +0. A sample dax is below, create something like this
Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))
Appreciate your kudos.
Mark my post as solution if this helps.
Hi NikhilKumar,
Thank you for your response and time
Actually i have tried with given logic but its look like still am getting blank instead of 0.
here is my updated dax.
Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))
statuscount=CALCULATE(count(table(productservice)+0,'Table'[product service] in DISTINCT('Table[[product service]))
here is how the data look like for some example
product status product service
open phase1
open phase1
open phase1
completed phase 2
completed phase 2
completed phase 2
completed phase 2
after adding data into matrix :
Product status open completed
product service
phase1 3
phase2 4
in above example i would like show 0 when there is no data associated with rows and columns
my expected output like below:
Product status open completed
product service
phase1 3 0
phase2 0 4
rows iam using as product service , columns using as Product status and values given as count(product service)
here to avoid blanks i have used adding +0 in count(product service),tried with comparing length of the count and also tried with new logic suggested by you. but still iam not able to show 0 intstead of blank .
please check and suggest your ideas. Thank you.
Thanks,
Mahi 1827
Hi @Mahi1827 ,
If possible can you share pbix removing sensitive data or a sampke pbix?
Appreciate your kudos!!
Hi Nikhil,
Thanks for your time, please check the sample file below. I have tried with both measure and measure 2 but still i am not able to show 0 instead of blank vallues.
https://1drv.ms/u/s!AlARJq6xd5KedVlP0zJpo2xgG_s?e=tR2Okt
Please share your ideas and solution. Thank you.
Thanks,
Mahi1827
Hi Nikhil,
This logic is working as expected.Thank you so much for your time and help on this.
Thanks,
Mahi
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |