Skip the complaints / background and jump to the questions below....
I am at my witt's end and ready to give up on Power BI. I have a working Power BI file with 4 reports but it's a complete mess, several fact tables, duplicated columns, and severly un-optimized. It holds about 1.5 million rows of transactions (Main Fact Table) for about 30k items (Secondary DIM table)
I created a new file to update and clean a lot of my data, dax, measures, calculated columns (CC), etc. I went from a snowflake schema to a star scheme with 1 fact table. I added a custom FY date table to replace the numerous calculated date columns and I am also using the trick of putting all of my meaures in one table to make it more clean (is this a problem?)
I keep getting circular reference problems when I copy the calculated columns / measures over from my old file (with alterations of course) into the new file. I can't get ANYTHING to work properly. Most of troubleshooting I've done stated to change the calculated columns over to a Measure, but now I can't use those in a chart/graph.
Questions:
I have 2 measures and 1 CC in my previous report that worked fine without a circular reference error (different fact tables) They are:
Capture the median receipts of an item over time
Median Receipts M =
CALCULATE(
MEDIAN(InvTx[Qty]),
InvTx[Tx Type] = "RECEIVING",
ALLEXCEPT( InvTx, InvTx[Station], InvTx[Other Inv Name], InvTx[Item], InvTx[Inv Name], InvList[Type of Inv] )
)
Calculate the difference between the current transaction and the median receipt of that item (could this be a CC?)
% Diff Median Receipts M =
IF( SUM(InvTx[Qty]) = 0, "0 Qty Rec",
IF( SUM( InvTx[Qty]) < 0, "Neg Rec",
ABS( SUM(InvTx[Qty]) / [Median Receipts] ) -1
)
)Finally, a CC to asign each transaction to a category
Tx Category CC =
IF( InvTx[Qty] > InvTx[PAR], "Rcpt Over PAR",
IF( InvTx[% Diff Median Receipts] = "0 Qty Rec", "0 Rcpt",
IF( InvTx[% Diff Median Receipts] = "Neg Rec", "Neg Rcpt",
IF( InvTx[Median Receipts] = 0, "0 Median",
IF(InvTx[% Diff Median Receipts] < -.70, "Below",
IF(InvTx[% Diff Median Receipts] >= -.70 && InvTx[% Diff Median Receipts] < .70, "Average",
IF(InvTx[% Diff Median Receipts] >= .70, "Above"
)))))))
As I stated, this produces a circular reference error when the CC is in the Fact Table and the 2 Measures are in a separate Measures Table. The circular reference is calling out this unrelated CC (Which works just fine):
This is an unrelated CC that seeks to assign a category to the Item Table for each item based on how long ago it was scanned. This CC is located in my Fact Table (is this where I'm going wrong?? I may have the wrong impression that everything should go in the Fact Table and not the DIM tables)
Scan Category CC =
IF( ISBLANK( [Days Last Scan] ), "3.Greater than 90 Days",
IF( [Days Last Scan] > 90, "3.Greater than 90 Days",
IF( [Days Last Scan] > 35, "2.Greater than 35", "1.Recent"
)))
This is the Measure used in the CC above to calculate the number of days. It's located in my Measures Table.
Days Last Scan =
CALCULATE(
DATEDIFF(
LASTDATE( InvTx[Date]), LASTDATE( InvItem[Query Date] ) , DAY ),
InvTx[Tx Type] = "PHYSICAL COUNT" || InvTx[Tx Type] = "USAGE"
)
FINALLY.... This is the Measure that I've created in the Measures table to replace the CC above that is giving me the Circular Reference Problem. This was the solution suggested on other threads. It works in a table and gives the accurate result but Power BI will not let me insert this in a graph visual. UGH!
Tx Category =
IF( MAX( InvTx[Qty] ) > MAX( InvTx[PAR] ), "Rcpt Over PAR",
IF( SUM( InvTx[Qty] ) = 0, "0 Rcpt",
IF( SUM( InvTx[Qty] ) < 0, "Neg Rcpt",
IF( 'Measures 1'[Median Rcpt] = 0, "0 Median",
IF( ABS( SUM(InvTx[Qty]) / [Median Rcpt] ) -1 < -.75, "Below",
IF( ABS( SUM(InvTx[Qty]) / [Median Rcpt] ) -1 >= -.75 && ABS( SUM(InvTx[Qty]) / [Median Rcpt] ) -1 < .75, "Average",
IF( ABS( SUM(InvTx[Qty]) / [Median Rcpt] ) -1 >= .75, "Above"
)))))))
I don't just want to fix the above problem.. i want to understand where my thought process is wrong because obviously I'm missing something!
I can send the PBI files individually if that will help anyone (that's the only way my co will allow it)
