Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
Please help me with the situation as below:
Produce the table as below, however my measure for Total #APP looks wrong, and i spent many day searching without possible solution:
Result
Date Slide from the DISB table.
CMP_NM column from the LEADGEN table
My Dax code as follow:
Total #APP = CALCULATE(DISTINCTCOUNT(APP[APP_ID_C]),LEADGEN[REALNM_NO]<>BLANK())
Total #DISB = CALCULATE(DISTINCTCOUNT(DISB[agreementid]),LEADGEN[REALNM_NO]<>BLANK())
Total #LEAD = CALCULATE(COUNT(LEADGEN[CUST_NO]),ALLEXCEPT(LEADGEN,LEADGEN[CMP_NM]))
Your help are greatly appreciated.
Thank you so much,
Solved! Go to Solution.
Hi @vpnnm,
Try this one, please. Are you sure the result should be 2901? I would suggest you check them with a small dataset.
Total #APP = CALCULATE ( DISTINCTCOUNT ( APP[APP_ID_C] ), FILTER ( BRIDGE, BRIDGE[ID] <> BLANK () ), FILTER ( LEADGEN, LEADGEN[REALNM_NO] <> BLANK () ) )
Best Regards,
do numbers change to correct ones if you switch the relationship to single direction for APP and DISB tables?
Unfortunately, result is same as before.
ps. The bridge table is appended of DISB and APP.
can you share sample rows from each table?
Yes, here is the file pbix
\
is anyone able to figure it out ? please help!
Hi @vpnnm,
I hope you didn't share anything confidential here.
Maybe you should count the [CIF_NO] instead. Please refer to the formula and snapshot below.
Total #APP 2 = CALCULATE ( DISTINCTCOUNT ( APP[CIF_NO] ), LEADGEN[REALNM_NO] <> BLANK () )
Best Regards,
Tks for your advice, it is dummy data anyway!
btw, it’s supposed to calculate the APP(APP_ID) not CIF_NO because 1 CIF can have multiples APP_ID
Also, i would like to use the DATE slider (DISBURSALDATE column of the DISB table) to filter but result is like this:
Hi @vpnnm,
What should the result be?
I think you need a date table in this scenario. It will connect to three tables just like the Bridge table. Finally, the result will respond to the date slicer that is from the calendar table.
Best Regards,
hi,
i try to achieve the result as below with your suggestion of making DATE table but still doesnt work. Thank you so much for your further supports.
Hi @vpnnm,
I found something after digging into your model.
1. The Creation_Date is a Datetime type. We need to convert it into a Date type column first by adding a new column.
Creation_Date_New = [CREATION_DATE].[Date]
2. Create a proper relationship like below. I think the one below should be the one in your scenario.
3. The field of the slicer should be from the Calendar table.
Best Regards,
Thank you for your effort.
However, the Total #App still look wrong ,right?
Hi @vpnnm,
Try this one, please. Are you sure the result should be 2901? I would suggest you check them with a small dataset.
Total #APP = CALCULATE ( DISTINCTCOUNT ( APP[APP_ID_C] ), FILTER ( BRIDGE, BRIDGE[ID] <> BLANK () ), FILTER ( LEADGEN, LEADGEN[REALNM_NO] <> BLANK () ) )
Best Regards,
Thank you so much, i learned a valuable lesson.
anyone?
can you share the data snapshot from each table (can be just few rows each) with the expected results for that sample?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |