How to obtain fact data?

Question

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

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

user9517769 · Accepted Answer · 2024-11-05 19:08:20Z

0

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]))
)

answered Nov 5, 2024 at 19:08

user9517769

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to obtain fact data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related