Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nit101
Frequent Visitor

Calculating total from related dataset

Hi,

I’m learning Power BI and I can’t work out how to get total from one of my datasets. 

I have 2 sliders – date uses vCalendar dataset and programme names uses vProgrammes dataset. 

My main report contains data from vPivotAttendance-Participant. 

nit101_0-1703260557777.png

Table 1 below shows the filter values I want to use in the measure.

I need to calculate the total number of days for each programme from vProgrammeDelivery that matches programme in vPivotAttendance-Participant.

Table 2 below shows the ProgSessionDay I want.

 

ProgSessionDay = COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate]))

 

When I add the measure in table 3, it show the Total for all participants ignoring the filters, so brings up all programmes.

This measure gives me the grand total from table 2 column ProgSessionDay. :

var _days =    

    CountX(

       KEEPFILTERS(VALUES(vProgrammeDeliveryDates[StartDate] ))

       , [ProgFilter]

    )

 

nit101_1-1703260557781.png

Please could someone help me work this out.

Thanks

 

8 REPLIES 8
nit101
Frequent Visitor

Hi @v-junyant-msft Dino,

 

Sorry for the delay to your response. Your Dax is nearly what I what, it counts the total number of dates for a ProgrammeShortName, but I also need the date filter from the Slider.  I thought this might work but it is not filtering on date:

 

TotalProgramDays =
    CALCULATE(
        COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate])),
        FILTER(
            ALL(vProgrammeDeliveryDates),
            vProgrammeDeliveryDates[ProgrammeShortName] = ('vPivotAttendance-Participant'[PSN_ParticipantSingle])
            && vProgrammeDeliveryDates[StartDate] >= MINX( 'vCalendar', vCalendar[TheDate] )
            && vProgrammeDeliveryDates[StartDate] <= MAXX( 'vCalendar', vCalendar[TheDate] )
        )
    )
 
vCalendar is a standard date dataset that lists all dates for the slider.
 
I'm working on getting your test data - reducing the dataset size for you.
 
Thank you for your help.
 
nit101
Frequent Visitor

Hi @v-junyant-msft / @lbendlin 

I have shared my report and sampl data in Dropbox folder:

https://www.dropbox.com/scl/fo/1ddo4r40c04hkuvl4ua5i/h?rlkey=vdko20yqtg2ujyfl2q7zrs1b8&dl=0

 

Thanks in advance for helping

link asks for credentials

Let's do some basic data quality checks first.  Your dimension table is missing the Eastlea - BB  offering.  You also have cases where you didn't offer the programme yet still had participants.  Please clarify.

 

lbendlin_0-1705422124762.png

 

I removed some data from the model for the sample, so not all the data in thedatesets will relate.  All the data in the live datasets relate. 

Yes, Eastlea - BB is missing from the dimestion table.  I kept some fact data for Eastlea - BB because when I was adding some measures to the visual, I was getting data for programmes that were not selected in the slider.

v-junyant-msft
Community Support
Community Support

Hi @nit101 ,

Please try this DAX:

TotalProgramDays = CALCULATE(
    COUNTROWS(VALUES(vProgrammeDeliveryDates[StartDate])),
    FILTER(
        ALL(vProgrammeDeliveryDates),
        vProgrammeDeliveryDates[ProgrammeName] IN VALUES(vPivotAttendance-Participant[ProgrammeName])
    )
)

But as @lbendlin  says, please provide sample data, which will be helpful!

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

lbendlin
Super User
Super User

your data model looks reasonable.  Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors