topic Re: DAX studio explain in Developer

DAX studio explain

Analitika — Wed, 14 Jul 2021 06:06:38 GMT

Why in dax studio All queries showing code not work in measure if i copy paste it from dax studio?

For example i take from dax variable and put it in measure

  VAR __DS0Core =
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'tbl_comp_info'[Company_Name],
          'tbl_comp_info'[Detailed financials],
          'tbl_comp_info'[Name]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      __ValueFilterDM3,
      "MedianLQ_Revenue_growth", CALCULATE(MEDIAN('tbl_comp_ratios_others'[LQ Revenue growth])),
      "SumTTM_Net_profit", CALCULATE(SUM('tbl_comp_ratios_others'[TTM Net profit])),
      "MedianLQ_Net_profit_growth", CALCULATE(MEDIAN('tbl_comp_ratios_others'[LQ Net profit growth])),
      "SumMarket_Cap", CALCULATE(SUM('tbl_comp_ratios_others'[Market_Cap])),
      "SumEV", CALCULATE(SUM('tbl_comp_ratios_others'[EV])),
      "MedianEV_EBITDA", CALCULATE(MEDIAN('tbl_comp_ratios_others'[EV/EBITDA])),
      "MedianEV_FCF", CALCULATE(MEDIAN('tbl_comp_ratios_others'[EV/FCF])),
      "MedianP_FCF", CALCULATE(MEDIAN('tbl_comp_ratios_others'[P/FCF])),
      "MedianP_E", CALCULATE(MEDIAN('tbl_comp_ratios_others'[P/E])),
      "MedianP_S", CALCULATE(MEDIAN('tbl_comp_ratios_others'[P/S])),
      "MedianP_BV", CALCULATE(MEDIAN('tbl_comp_ratios_others'[P/BV])),
      "MedianDividend_yield", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Dividend yield])),
      "MedianGross_margin", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Gross margin])),
      "MedianOperating_margin", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Operating margin])),
      "MedianEBITDA_margin", CALCULATE(MEDIAN('tbl_comp_ratios_others'[EBITDA margin])),
      "MedianNet_margin", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Net margin])),
      "MedianROE", CALCULATE(MEDIAN('tbl_comp_ratios_others'[ROE])),
      "MedianROA", CALCULATE(MEDIAN('tbl_comp_ratios_others'[ROA])),
      "MedianROCE", CALCULATE(MEDIAN('tbl_comp_ratios_others'[ROCE])),
      "MedianROIC", CALCULATE(MEDIAN('tbl_comp_ratios_others'[ROIC])),
      "MedianInventory_turnover", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Inventory turnover])),
      "MedianAcc_receivable_turnover", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Acc.receivable turnover])),
      "MedianAcc_payable_turnover", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Acc.payable turnover])),
      "MedianCash_conversion_cycle", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Cash conversion cycle])),
      "SumWorking_Capital", CALCULATE(SUM('tbl_comp_ratios_others'[Working Capital])),
      "MedianEquity_ratio", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Equity ratio])),
      "MedianLiquidity_ratio", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Liquidity ratio])),
      "MedianQuick_ratio", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Quick ratio])),
      "MedianLiabilities_Equity", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Liabilities/Equity])),
      "MedianNET_DEBT", CALCULATE(MEDIAN('tbl_comp_ratios_others'[NET DEBT])),
      "MedianNet_debt_EBITDA", CALCULATE(MEDIAN('tbl_comp_ratios_others'[Net debt/EBITDA])),
      "MedianICR", CALCULATE(MEDIAN('tbl_comp_ratios_others'[ICR])),
      "MedianDSCR", CALCULATE(MEDIAN('tbl_comp_ratios_others'[DSCR])),
      "Market_Place", 'tbl_comp_info'[Market Place],
      "Super_sector", 'tbl_comp_info'[Super sector],
      "Industry_m", 'tbl_comp_info'[Industry_m],
      "Last_reported_quarter", 'tbl_comp_info'[Last reported quarter],
      "SumTTM_FCF", CALCULATE(SUM('tbl_comp_ratios_others'[TTM FCF])),
      "SumTTM_OP_CF", CALCULATE(SUM('tbl_comp_ratios_others'[TTM OP_CF])),
      "SumTTM_FFO", CALCULATE(SUM('tbl_comp_ratios_others'[TTM FFO])),
      "Exchange_list", 'tbl_comp_info'[Exchange list],
      "MinAttribute", CALCULATE(MIN('tbl_comp_ratios_others'[Attribute])),
      "SumLQ_Revenue_growth", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[LQ Revenue growth]))),
      "SumLQ_Net_profit_growth", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[LQ Net profit growth]))),
      "SumEV_EBITDA", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[EV/EBITDA]))),
      "SumEV_FCF", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[EV/FCF]))),
      "SumP_FCF", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[P/FCF]))),
      "SumP_E", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[P/E]))),
      "SumP_S", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[P/S]))),
      "SumP_BV", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[P/BV]))),
      "SumDividend_yield", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[Dividend yield]))),
      "SumNET_DEBT", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[NET DEBT])))
    )

I remve all flters

__DS0FilterTable2,
__ValueFilterDM3,

but get error on rollups

An why dax studio converting real formulas in measure to something strange, for example
Real formula is

LQ Revenue growth = divide(tbl_comp_ratios_others[Revenue]-tbl_comp_ratios_others[Revenue ly],tbl_comp_ratios_others[Revenue ly])

Convert to
"MedianLQ_Revenue_growth", CALCULATE(MEDIAN('tbl_comp_ratios_others'[LQ Revenue growth])),

And what means "SumLQ_Revenue_growth", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[LQ Revenue growth]))),

Re: DAX studio explain

Anonymous — Fri, 16 Jul 2021 02:29:15 GMT

Hi @Analitika ，

Due to the above query in your post will return multiple rows data, it should be as a table. So please create a calculated table instead of creating a measure with the below formula:

tab = VAR __DS0FilterTable = FILTER ( KEEPFILTERS ( VALUES ( 'tbl_Companies'[Company_Name] ) ), NOT ( ISBLANK ( 'tbl_Companies'[Company_Name] ) ) ) VAR __DS0Core = SUMMARIZECOLUMNS ( ROLLUPADDISSUBTOTAL ( ROLLUPGROUP ( 'tbl_comp_info'[Company_Name], 'tbl_comp_info'[Detailed financials], 'tbl_comp_info'[Name] ), "IsGrandTotalRowTotal" ), __DS0FilterTable, "MedianLQ_Revenue_growth", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[LQ Revenue growth] ) ), "SumTTM_Net_profit", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[TTM Net profit] ) ), "MedianLQ_Net_profit_growth", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[LQ Net profit growth] ) ), "SumMarket_Cap", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[Market_Cap] ) ), "SumEV", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[EV] ) ), "MedianEV_EBITDA", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[EV/EBITDA] ) ), "MedianEV_FCF", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[EV/FCF] ) ), "MedianP_FCF", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[P/FCF] ) ), "MedianP_E", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[P/E] ) ), "MedianP_S", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[P/S] ) ), "MedianP_BV", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[P/BV] ) ), "MedianDividend_yield", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Dividend yield] ) ), "MedianGross_margin", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Gross margin] ) ), "MedianOperating_margin", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Operating margin] ) ), "MedianEBITDA_margin", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[EBITDA margin] ) ), "MedianNet_margin", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Net margin] ) ), "MedianROE", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[ROE] ) ), "MedianROA", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[ROA] ) ), "MedianROCE", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[ROCE] ) ), "MedianROIC", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[ROIC] ) ), "MedianInventory_turnover", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Inventory turnover] ) ), "MedianAcc_receivable_turnover", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Acc.receivable turnover] ) ), "MedianAcc_payable_turnover", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Acc.payable turnover] ) ), "MedianCash_conversion_cycle", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Cash conversion cycle] ) ), "SumWorking_Capital", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[Working Capital] ) ), "MedianEquity_ratio", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Equity ratio] ) ), "MedianLiquidity_ratio", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Liquidity ratio] ) ), "MedianQuick_ratio", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Quick ratio] ) ), "MedianLiabilities_Equity", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Liabilities/Equity] ) ), "MedianNET_DEBT", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[NET DEBT] ) ), "MedianNet_debt_EBITDA", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[Net debt/EBITDA] ) ), "MedianICR", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[ICR] ) ), "MedianDSCR", CALCULATE ( MEDIAN ( 'tbl_comp_ratios_others'[DSCR] ) ), "Market_Place", 'tbl_comp_info'[Market Place], "Super_sector", 'tbl_comp_info'[Super sector], "Industry_m", 'tbl_comp_info'[Industry_m], "Last_reported_quarter", 'tbl_comp_info'[Last reported quarter], "SumTTM_FCF", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[TTM FCF] ) ), "SumTTM_OP_CF", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[TTM OP_CF] ) ), "SumTTM_FFO", CALCULATE ( SUM ( 'tbl_comp_ratios_others'[TTM FFO] ) ), "Exchange_list", 'tbl_comp_info'[Exchange list], "MinAttribute", CALCULATE ( MIN ( 'tbl_comp_ratios_others'[Attribute] ) ), "SumLQ_Revenue_growth", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[LQ Revenue growth] ) ) ), "SumLQ_Net_profit_growth", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[LQ Net profit growth] ) ) ), "SumEV_EBITDA", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[EV/EBITDA] ) ) ), "SumEV_FCF", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[EV/FCF] ) ) ), "SumP_FCF", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[P/FCF] ) ) ), "SumP_E", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[P/E] ) ) ), "SumP_S", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[P/S] ) ) ), "SumP_BV", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[P/BV] ) ) ), "SumDividend_yield", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[Dividend yield] ) ) ), "SumNET_DEBT", IGNORE ( CALCULATE ( SUM ( 'tbl_comp_ratios_others'[NET DEBT] ) ) ) ) RETURN __DS0Core

And for the meaning of below formula, you can refer the content in this link to understand it.

"SumLQ_Revenue_growth", IGNORE(CALCULATE(SUM('tbl_comp_ratios_others'[LQ Revenue growth]))),

Best Regards