CODEWITHSUNDEEP
powerbidaxrelationshippowerbi-desktopdaxstudio
David Molina
David Molina

Reputation: 161

How to obtain fact data?

I have a model like the one in the picture where the arrows indicate the direction of the filter. I am trying to get the data from my fact tables so that, given a range of dates (which I get through a filter), I can get a table with which farmer, has which farm and in each farm which zones. For example: In a date range from 2024/01/01 to 2024/01/31, farmer g1 had a farm g1 with 4 zones z1-z4 and from each farm get its max_capacity and from each zone the max_capacity. So I can see a history of the capacity of each zone of the farm.

About zones, I've tried this code:

zone_max_capacity = 
    CALCULATE(
            MAX(fact_zone_params[max_capacity]),
            CROSSFILTER(dim_zone[id_zone], fact_farm_zone_farmer[id_zone], Both),
            CROSSFILTER(fact_zone_params[id_zone], dim_zone[id_zone], Both)
    )

enter image description here

obviously I only get one result, because of the max, but I would like to get all the ones that appear so that I have a table like this:

farmer max_capacity zone date max_capacity
f1 500 z1 2024/01/01 25
f2 300 z1 2024/01/01 20
f2 300 z1 2024/01/02 50
f2 300 z1 2024/01/03 70
f3 100 z1 2024/01/08 100

This way I can see something similar to a history where I know what the maximum capacity was for each zone of each farm and whose farm it was. Note that in the case of farmer2 and zone 1 the maximum capacity has changed.

I hope I have explained myself. Thank you very much in advance

Upvotes: 0

Views: 31

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

You need to a date filter that limits the data to the specific date range you want from 2024/01/01 to 2024/01/31.

For your DAX I would go for using SUMMARIZE or ADDCOLUMNS with CALCULATE to get the history :

History_Capacity_View = 
VAR DateRange = 
    DATESBETWEEN(dim_date[Date], DATE(2024, 1, 1), DATE(2024, 1, 31))
RETURN
SUMMARIZE(
    FILTER(fact_zone_params, fact_zone_params[Date] IN DateRange),
    dim_farmer[Farmer],
    fact_farm_zone_farmer[Farm],
    dim_zone[Zone],
    fact_zone_params[Date],
    "Farm_Max_Capacity", CALCULATE(MAX(fact_farm_params[max_capacity])),
    "Zone_Max_Capacity", CALCULATE(MAX(fact_zone_params[max_capacity]))
)

Upvotes: 0

Related Questions