Calculating total from related dataset

[nit101]

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. 

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]

    )

Please could someone help me work this out.

Thanks

[nit101]

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]

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

[lbendlin]

link asks for credentials

[nit101]

Sorry, try this link https://www.dropbox.com/scl/fo/qarzjxz62v3107u7mtzer/h?rlkey=3ufoqgghg66mdbh3dcg7bdcoj&dl=0 

[lbendlin]

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.

[nit101]

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]

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]

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