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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chris_Cools
Frequent Visitor

Need help for my model: management wants figures based on dimensions that don't actually exist

Hello,

 

i am thinking about making the switch from Qlikview to Power Bi and at the moment reading up on how to make a good model.

 

Now i have this to think through.

 

I have a FactInvoice table:

Customer IDSKU IDqty price
1134,5
1225
2134,5
2225
334

3,5

...  

 

 

DimCustomer: (this in total has 

Customer IDCustomertype
1Retail
2Project
3e-commerce
4Wholesale
5other

 

DimProduct:

SKU IDProductCategory
1Tableware
2Furniture
3Decoration
4Candles
5Textile
6Wall art
7Other

 

Now i need to show total turnover out of all the invoices in 4 figures, each representing customers or products needing a different approach:

1 = Turnover for all Customers where Customertype = "Project" and ProductCategory = "Tableware"

2 = Turnover for all Customers where Customertype = "Project" and ProductCategory = "everything except Tableware"

3 = Turnover for all Customers where Customertype = "everything except Project" and ProductCategory = "Tableware"

4 = Turnover for all Customers where Customertype = "everything except Project" and ProductCategory = "everything except                    Tableware"

 

So managent want's to have a look at 4 figures based on a logic that is not 100% based on slicing by 1 dimension.

Reason is they are not ready to rethink used dimensions at this moment but want to have an alternative clustering.

I understand that these 4 figures can be calculated with measures based on turnover in the FactInvoice table and filtered based on the dimensions in the 2 dimension tables.

 

And if i then make a table with dimension year and these 4 measures evolution can be viewed for these amounts.

But what if other metrics want to be viewed for these new 4 categories of customer/product combinations?

for example:

          -how many customers in each of the 4 groups

          -amount of outstanding orders for these 4 groups

           -...

That would mean there is a need to have these 4 categories as a seperate dimension field?

But since it is a dimension based on a combination of facts customertype and productype how would i need to tackle this?

Should i add logic to the FactInvoice table so that on every invoice line i have an indication to which of the 4 categories the line belongs to? 

But if i do it like that am i then not creating a dimension in a fact table?

 

Could someone help me solve this?

 

thanx a lot!!

 

Chris

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Chris_Cools , Create a new column in your fact

Switch(True(),

Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) = "Tableware" , 1 ,

Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) <> "Tableware" , 2 ,

Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) = "Tableware" , 3 ,

Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) <> "Tableware" , 4

 

Add other rules if needed

 

You can now use this column from you fact.

 

The same can be done in power query, by merging the tables and getting these columns in the fact

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

or using 

Power Query Table.AddJoinColumn: https://youtu.be/-mThmfSaiV8

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Chris_Cools , Create a new column in your fact

Switch(True(),

Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) = "Tableware" , 1 ,

Related(Customer[Customertype]) = "Project" && Related(Product[ProductCategory]) <> "Tableware" , 2 ,

Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) = "Tableware" , 3 ,

Related(Customer[Customertype]) <> "Project" && Related(Product[ProductCategory]) <> "Tableware" , 4

 

Add other rules if needed

 

You can now use this column from you fact.

 

The same can be done in power query, by merging the tables and getting these columns in the fact

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

or using 

Power Query Table.AddJoinColumn: https://youtu.be/-mThmfSaiV8

Hello Amitchandak,

 

Thank you very much for your reply and i will test this solution!

But i was wondering, when done like this does this go against the logic of not having dimensions in the fact table?

Or is this no issue at all?

 

kind regards,

chris

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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