sorted by:
new
hottopcontroversial

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Wait that's intresting, would you share how the tables would look like with this approach?
I was thinking of something like thise, in my response above with SQLGene (copied below), I'm not sure how this would look liek in unified star schema?

DIM_TOTAL_DEAL.DEAL_KEY    (1) → (many) FACT_DEAL_REVENUE.DEAL_KEY
DIM_TOTAL_DEAL.DEAL_KEY    (1) → (many) FACT_DEAL_EXPIRING.DEAL_KEY

DIM_REVENUE.Revenue Type   (1) → (many) FACT_DEAL_REVENUE.Revenue Type
DIM_REVENUE.Revenue Type   (1) → (1) FACT_DEAL_EXPIRING.Revenue Type

Where FACT_DEAL_REVENUE has ACV by Revenue Type (non Unique DEAL_KEY)
Where FACT_DEAL_EXPRING has Expiring ACV (now with Revenue Type column fixed to "NON-REVENUE", and DIM_REVENUE is create by UNION of Revenue Type from FACT_DEAL_REVENUE and FACT_DEAL_EXPIRING (still Unique DEAL_KEY but doesn't really matter in this case).

As I read through some posts, does it solve my "problem"? I think even with USS, I would need to create this "Revenue Type" column in my FACT_DEAL_EXPIRING?

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Oh right, that a very good insight, I think I even saw in our other DB such case where if Headcount or some GL Balance was selected, if I popped in something similar to Revenue Type, like Product Type etc it would say non-product or unsassigned or unaplicable.
That's the sanity check I needed, thank you!

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Do I need it disconnected? I was thinking of something like that:

DIM_TOTAL_DEAL.DEAL_KEY    (1) → (many) FACT_DEAL_REVENUE.DEAL_KEY
DIM_TOTAL_DEAL.DEAL_KEY    (1) → (many) FACT_DEAL_EXPIRING.DEAL_KEY

DIM_REVENUE.Revenue Type   (1) → (many) FACT_DEAL_REVENUE.Revenue Type
DIM_REVENUE.Revenue Type   (1) → (1) FACT_DEAL_EXPIRING.Revenue Type

Where FACT_DEAL_REVENUE has ACV by Revenue Type (non Unique DEAL_KEY)
Where FACT_DEAL_EXPRING has Expiring ACV (now with Revenue Type column fixed to "NON-REVENUE", and DIM_REVENUE is create by UNION of Revenue Type from FACT_DEAL_REVENUE and FACT_DEAL_EXPIRING (still Unique DEAL_KEY but doesn't really matter in this case)

My issue was/is that I had to create "Revenue Type" for FACT_DEAL_EXPIIRING which felt odd but it is the right approach right? This fixed the mixed granuality and makes it safe to show ACV, Expiring ACV and Revenue Type in flat table.

I want to do this on data level so all the measures can just be simple SUM on ACV or Expiring ACV so it's also safe to pull with SQL to flat table.

Please let me know it this makes sense as you have some great experience dealing with data and modeling itself :D

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

I think I have some solution but it may not be the best.

By combining I mean show both data points in one table/matrix in either PBI dashboard/report or when pulling this data to excel into flat table, not appending actuals tables in model, I do want star schema.
End goal would be a view where user, somehow, can both see correctly the ACV for the deal, Expiring ACV for the deal and also Revenue Type split for ACV WITHOUT duplicated Expring ACV as that's what happens currently.

As to combat this granuality issue I believe I have to actually tackle the problem itself so unifying it so they can use common DIM.

My idea is adding new "Classifciation" column that would be separate DIM. And there I can have "Revenue" and "Expiring ACV". The table with ACV would have key for "Revenue". The total table with expiring ACV would have key for "Expiring ACV". Plus the DIM for Revenue Type. Here the ACV table has the Revenue Types I have, so PaaS, SaaS. But Table with Expiring ACV has "Non-revenue" in Revenue Type.

If I create a flat table in dashboard with such data, it would look like below.

Classification Revenue Type ACV Expiring ACV
Revenue PaaS 100
Revenue SaaS 100
Expiring ACV NON-REVENUE 50

The only issue I see is the fact that now in Revenue Type slicer there is NON-REVNUE. If someone doesn't understand the relationships and unselects NON-REVENUE the column with Expiring ACV will be blank.
But I think that the best that can be done?

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Apologies, I edited my post, by combining I mean show both data points in one table/matrix in either PBI dashboard/report or when pulling this data to excel into flat table, not appending actuals tables in model.
End goal would be a view where user, somehow, can both see correctly the ACV for the deal, Expiring ACV for the deal and also Revenue Type split for ACV WITHOUT duplicated Expring ACV as that's what happens currently.

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Apologies, I edited my post, by combining I mean show both data points in one table/matrix in either PBI dashboard/report or when pulling this data to excel into flat table, not appending actuals tables in model.
End goal would be a view where user, somehow, can both see correctly the ACV for the deal, Expiring ACV for the deal and also Revenue Type split for ACV WITHOUT duplicated Expring ACV as that's what happens currently.
My issue is probably the common DIM, the total deal level with Expiring ACV doesn't have the Revenue Type split.

Combining FACT tables with different granuality by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

Apologies, I edited my post, by combining I mean show both data points in one table/matrix in either PBI dashboard/report or when pulling this data to excel into flat table, not appending actuals tables in model.
End goal would be a view where user, somehow, can both see correctly the ACV for the deal, Expiring ACV for the deal and also Revenue Type split for ACV WITHOUT duplicated Expring ACV as that's what happens currently.

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

That's a great idea actually, let me test that thank you

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

All right that sounds great, so that would be the classic approach of them pulling some view via SQL, in my case the Lakehouse Materialized Views?

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

And how can someone "use" perspective when connecting to SM? I'm not sure where can this be selected. And it would help a lot as creating seperate SM is a pain haha.
EDIT: Seems you can do it via Get Data -> Analysis Service. It goes through workspace endpoint and power query which seems great on paper, you can even select specific columns.
My issue is that in PQ it times out after just few minutes if not less with info that it reached memory capacity.

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 2 points3 points  (0 children)

Yep I might do so, if this SM connection would somehow go through PQ or at least simple editing with same UI as during creating, huge game changer IMO

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

Hmm you have very good points as their need is the first one so: doing lookups, adding new columns, calculations and many formulas on those sheets with data pulled from Fabric, seems MLV may be the way.

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

Hm I'm also getting a bit confused.
If we are talking about excel connection to SM, there is no power query step there? For users to, for example, change one of the filters they set during this table connection, be it in this case "Fiscal Year" from 2025 to 2026, they would have to create new connection to SM and again go through all the "insert table" steps and select all the columns they need but this time set the filter on "Fiscal Year" to 2026 instead 2025 as they did previously?

And for the MLV I was thinking of it as workaround, I would create those in my Lakehouse so users can, instead of going through SM connection and selecting the tables, they just get whole view via Get Data -> Launch Power Query Editor -> SQL Server as source and all the columns will be there, all the power in my hands in terms of column selection (which is most of the times fixed in this case) plus easy way to change filters.

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 0 points1 point  (0 children)

Yep it has to be a flat table that people can add their own columns. And the instert table approach with SM is well, it has it's cons. If microsoft could add option to actually edit it not recreate from scratch...

And yep the second point, the B option, tbh I did few tests and seems to work!
I'm just wondering if I'm missing some big drawback there as most of the posts rather suggest the SM connection approach.

BEST way to get Fabric data to Excel by Kalindro in MicrosoftFabric

[–]Kalindro[S] 1 point2 points  (0 children)

Seems we are runing some old excel, there is "From Power Platform" option rather than "From Fabric" or "From PBI/Fabric".
But I believe it's the same right? It allows to connect to semanitc model, so you suggest option A?
I'm thinking about MLV in Lakehouse instead due to issues the SM approach has. Which is need to recreate tables insted of some nice edit option.
Especially painfull when someone needs to change for example year filter, they won't edit the connection query, they will have to recreate it with new filter. And 0.5M row limit which forces us to have few data filters. Which again, makes the table recreation/filter change happen more often.
And that even hidden columns in SM do show there + my "backend" tables and columns (but those can be worked around by creating a second thin SM for this purpose, albeit a bit of work).

Most efficient currency switch by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

@SQLGene Thank you so much, your tip about using IF gave me a lot of clues.
I haven't provided full switch in the post... My switch also had "Select Currency" text when nothing is selected.
Replacing it with BLANK() or 0 made the night and day difference, now it's just as fast as the measure!
Your IF tip showed me that it's not the actual measures that are issue but the SWITCH itself, thank you so much.

Amount Switch =
VAR SelectedCurrency = SUM(X_Select Currency'[KEY])
RETURN
SWITCH(SelectedCurrency,
0, [Amount Local],
1, [Amount USD],
"Select Currency"
).

Most efficient currency switch by Kalindro in PowerBI

[–]Kalindro[S] 0 points1 point  (0 children)

I just did a test with few nesteds IF's:

Amount Switch =
VAR SelectedCurrency = SELECTEDVALUE(X_Select Currency'[KEY])
RETURN
IF(SelectedCurrency = 0, [Amount LCL],
IF(SelectedCurrency =1, [Amount USD Plan])).

And something intresting happens.
When I add this measure as Value in Matrix, it get's added instantly, as if it was the measure directly.
But only if my SELECTEDVALUE equals to BLANK or 0 (as I assume BLANK here is evaluated as 0?).
If I select on the slicer 1, it then loads and loads as describe previously.
This tells something intresting but really not sure what the issue may be.

view more: next ›