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.
Hello everybody!
I need to calculate the number of rows for a specific table (archive) that is linked to our sales master and calendar table.
The sales master has a date column is linked to our calendar and attribute table.
The sales master has this structure:
Order (key to Archive) | Date (Key to Calendar) | Article (Key to Attribute List) |
100 | 01.01.2021 | ABC |
This table contains a unique row per order number.
The archive table has this structure:
Order (key to Sales Master) | Date (Key to Calendar) |
100 | 01.01.2021 |
100 | 07.01.2021 |
The goal is to calculate the number of rows of this table per week based on some attributes out of the attribute table.
The archive table is linked (active) to the Calender and to Sales Master (Inactive).
This measure won't work since then the Sales master already has an active relation to the calendar.
# Rows =
CALCULATE(
COUNTROWS('Arhive'),
USERELATIONSHIP('Archive'[Order], 'Sales Master'[Order])
What would you propose?
How can I deactivate the relation between Sales master and Calendar within this measure?
Solved! Go to Solution.
Here's me playing around with a simple model. Attached a picture of it below.
// I understand Calendar is connected
// to Master on Date and Calendar is
// also connected to Archive. There's
// a connection from Master to Archive
// on OrderID. The connection from
// Calendar to Archive is active, the
// other one, from Master to Archive,
// is inactive. The Attribute dimension
// is also linked to Master on Article.
// The goal is to calculate the number of
// rows of this table (Archive) per week based
// on some attributes out of the attribute table.
DEFINE MEASURE Archive[Rowcount in Archive] =
CALCULATE(
COUNTROWS( Archive ),
// Deactivate Calendar -> Master
CROSSFILTER(
'Calendar'[Date],
Master[Date],
NONE
),
// Activate Master -> Archive
USERELATIONSHIP(
Master[OrderID],
Archive[OrderID]
)
)
EVALUATE
CALCULATETABLE(
{
[Rowcount in Archive]
},
'Calendar'[Date] = date(2020, 1, 1),
Master[OrderID] in {1, 5}
)
Here's me playing around with a simple model. Attached a picture of it below.
// I understand Calendar is connected
// to Master on Date and Calendar is
// also connected to Archive. There's
// a connection from Master to Archive
// on OrderID. The connection from
// Calendar to Archive is active, the
// other one, from Master to Archive,
// is inactive. The Attribute dimension
// is also linked to Master on Article.
// The goal is to calculate the number of
// rows of this table (Archive) per week based
// on some attributes out of the attribute table.
DEFINE MEASURE Archive[Rowcount in Archive] =
CALCULATE(
COUNTROWS( Archive ),
// Deactivate Calendar -> Master
CROSSFILTER(
'Calendar'[Date],
Master[Date],
NONE
),
// Activate Master -> Archive
USERELATIONSHIP(
Master[OrderID],
Archive[OrderID]
)
)
EVALUATE
CALCULATETABLE(
{
[Rowcount in Archive]
},
'Calendar'[Date] = date(2020, 1, 1),
Master[OrderID] in {1, 5}
)
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |