Forum Discussion

New Member

2 months ago

Deneb/vega on power bi

Hello, I am building a radar chart in Power BI using Deneb with Vega. The chart displays seven indicators: Precarious work rate AHT stability AHT entry rate AHT consumption AJT entry rate AJT...

BeaBF

Super User

2 months ago

Serizawa Hi!
The disconnected indicator table is correct and does not need a relationship. The issue is more likely that the measures used in the SWITCH do not all receive the filter from 't_pbi_dash'[entite_realisatrice].

As a first test, I would explicitly pass the selected entity to the fact table used by each measure with TREATAS:

Radar Value =
VAR _Indicator =
SELECTEDVALUE ( t_radar_Indicateurs[Indicateur] )

VAR _Entities =
VALUES ( 't_pbi_dash'[entite_realisatrice] )

RETURN
SWITCH (
_Indicator,

"Taux de précaires",
CALCULATE (
[_tx_precaire],
KEEPFILTERS (
TREATAS (
_Entities,
'FactPrecaire'[entite_realisatrice]
)
)
),

"Stab AHT",
CALCULATE (
[_tx_stabiliteAHT_All],
KEEPFILTERS (
TREATAS (
_Entities,
'FactAHT'[entite_realisatrice]
)
)
),

"Saisie AHT",
CALCULATE (
[_tx_saisie_sur_AHT],
KEEPFILTERS (
TREATAS (
_Entities,
'FactAHT'[entite_realisatrice]
)
)
),

"Consommation AHT",
CALCULATE (
[_tx_pris_AHT],
KEEPFILTERS (
TREATAS (
_Entities,
'FactAHT'[entite_realisatrice]
)
)
),

"Taux de saisie AJT",
CALCULATE (
[_tx_prises_sur_ajt],
KEEPFILTERS (
TREATAS (
_Entities,
'FactAJT'[entite_realisatrice]
)
)
),

"Consommation AJT",
CALCULATE (
[_tx_consommation_ajt],
KEEPFILTERS (
TREATAS (
_Entities,
'FactAJT'[entite_realisatrice]
)
)
),

"Tx consommation Fenêtres",
CALCULATE (
[_Tx_fenêtres_prises],
KEEPFILTERS (
TREATAS (
_Entities,
'FactFenetres'[entite_realisatrice]
)
)
)
)

The fact-table and column names must obviously be replaced with the actual ones. If this measure produces different radars, it confirms that the entity filter was not propagating to all the tables. The permanent solution would be to create a shared entity dimension related one-to-many to every fact table and use the entity field from that dimension in the visual.

For the 176.7%, I would check the AHT measure directly. It should return a decimal ratio and should not multiply by 100:

_tx_saisie_sur_AHT =
DIVIDE (
[Distinct AHT Entries],
[Distinct Expected AHT],
BLANK()
)

For example:

Distinct AHT Entries =
DISTINCTCOUNT ( 'FactAHT'[AHT_ID] )

Distinct Expected AHT =
DISTINCTCOUNT ( 'FactAHTExpected'[AHT_ID] )

If the numerator and denominator come from different tables, make sure they are both filtered by the same entity dimension. If the expected AHT table is not filtered by entity, apply the same entity filter with TREATAS.

I would then remove the conditional percentage conversion from Vega. Keep both measures and thresholds as decimals, use:

{
"name": "r",
"type": "linear",
"domain": [0, 1],
"range": [0, {"signal": "radius"}],
"clamp": true
}

and format the label with:

"format(datum.Taux, '.1%')"

If _tx_saisie_sur_AHT returns 1.767, then 176.7% is the real DAX result. In that case, the numerator is greater than the denominator, most likely because of duplicated AHT identifiers, different grains, or different filter contexts. Vega is only displaying the value it receives.

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

Serizawa

New Member

2 months ago

Thank you, this is very helpful.

I checked the DAX behind the measures, and I have a bit more information now.

For the entity-filter issue, most of the measures involved are actually calculated directly from the same fact table, t_pbi_dash, which is also the table containing the entity field used in the visual:

't_pbi_dash'[entite_realisatrice]

For example:

_tx_prises_sur_ajt =
DIVIDE([_pris], [_tout_ajt], 0)

with:

_pris =
VAR DateDebut = MIN('t_pbi_sequence_date'[date_serie])
VAR DateFin   = MAX('t_pbi_sequence_date'[date_serie])
RETURN
COALESCE(
    CALCULATE(
        COUNTA('t_pbi_dash'[id_op]),
        't_pbi_dash'[prise],
        't_pbi_dash'[titulaire_ou_associee],
        't_pbi_dash'[date_ajt_fin] >= DateDebut,
        't_pbi_dash'[date_ajt] <= DateFin
    ),
    0
)

and:

_tout_ajt =
VAR DateDebut = MIN('t_pbi_sequence_date'[date_serie])
VAR DateFin   = MAX('t_pbi_sequence_date'[date_serie])
RETURN
COALESCE(
    CALCULATE(
        COUNTA('t_pbi_dash'[id_op]),
        't_pbi_dash'[titulaire_ou_associee],
        't_pbi_dash'[date_ajt_fin] >= DateDebut,
        't_pbi_dash'[date_ajt] <= DateFin
    ),
    0
)

The same is true for _pris_saisi, _pris_aht, _precaire, etc. They all reference t_pbi_dash directly and do not currently use ALL, REMOVEFILTERS, or another unrelated fact table.

Because of that, I am now less convinced that TREATAS is necessary for these particular measures, since the filter on t_pbi_dash[entite_realisatrice] should already be part of the filter context.

I will still check the remaining measures, especially _stab_AHT and _Tx_fenêtres_prises, because those may use other tables.

Regarding the 176.7% value, I think I found a likely cause.

The rate is:

_tx_saisie_sur_AHT =
DIVIDE([_pris_saisi], [_tout_AHT], 0)

with:

_tout_AHT =
[_tout_ajt] - [_hors_prgm]

However, _pris_saisi currently includes rows that are outside the program:

_pris_saisi =
VAR DateDebut = MIN('t_pbi_sequence_date'[date_serie])
VAR DateFin   = MAX('t_pbi_sequence_date'[date_serie])
RETURN
COALESCE(
    CALCULATE(
        COUNTA('t_pbi_dash'[id_op]),
        't_pbi_dash'[pris_pas_pris_saisi],
        't_pbi_dash'[titulaire_ou_associee],
        't_pbi_dash'[date_ajt_fin] >= DateDebut,
        't_pbi_dash'[date_ajt] <= DateFin
    ),
    0
)

So the numerator can contain “hors programme” rows while the denominator explicitly removes them.

In other words, the current calculation is effectively:

entered AHT, including out-of-program rows
------------------------------------------
total AHT excluding out-of-program rows

That seems like a plausible explanation for a value greater than 100%.

My next steps are therefore:

make _pris_saisi use the same program scope as _tout_AHT;
compare each rate by entite_realisatrice in a normal Power BI table before looking at Deneb;
inspect the remaining measures that may use other tables;
only use TREATAS where the entity filter genuinely needs to be propagated to another table.

So at this stage, Vega seems unlikely to be the source of either issue: the 176.7% appears to come from the DAX ratio itself, and the identical radars may only concern some of the measures rather than all of them.

BeaBF
Super User
1 day ago
Hi Serizawa ,

Thanks for sharing the measures. Your diagnosis is right. Here are a few points to help you close both issues.

The 176.7%

The numerator and the denominator don't count the same set of rows. _tout_AHT removes the out-of-programme rows, but _pris_saisi doesn't, so the ratio can go above 100%. The safest fix is to apply the same scope to both sides, in the same way:

_pris_saisi_hors_prgm =
VAR DateDebut = MIN ( 't_pbi_sequence_date'[date_serie] )
VAR DateFin = MAX ( 't_pbi_sequence_date'[date_serie] )
RETURN COALESCE ( CALCULATE ( COUNTA ( 't_pbi_dash'[id_op] ), 't_pbi_dash'[pris_pas_pris_saisi], 't_pbi_dash'[titulaire_ou_associee], 't_pbi_dash'[date_ajt_fin] >= DateDebut, 't_pbi_dash'[date_ajt] <= DateFin, <same out-of-programme condition used in _hors_prgm> ), 0 ) _pris_saisi_prgm = [_pris_saisi] - [_pris_saisi_hors_prgm] _tx_saisie_sur_AHT = DIVIDE ( [_pris_saisi_prgm], [_tout_AHT] )

Also check these:

_hors_prgm must use exactly the same filters as _tout_ajt (titulaire_ou_associee and the date overlap). If it doesn't, the subtraction mixes two scopes, and the denominator can end up too small.

COUNTA vs DISTINCTCOUNT. COUNTA('t_pbi_dash'[id_op]) counts rows, not operations. If an id_op can appear on more than one row (one row per date, status or person, for example), compare COUNTROWS with DISTINCTCOUNT('t_pbi_dash'[id_op]) for one entity. If the two numbers differ, use DISTINCTCOUNT in both the numerator and the denominator.

A temporary check measure shows you every place where the issue remains: Check AHT = IF ( [_pris_saisi_prgm] > [_tout_AHT], "Numerator > Denominator" )

Identical radars

You're right: measures built only on t_pbi_dash already receive the filter on 't_pbi_dash'[entite_realisatrice], so they don't need TREATAS. That points the identical values at three possible causes:

Measures that read other tables (_stab_AHT, _Tx_fenêtres_prises). Those tables can't be filtered by a column that lives in another fact table, so they return the same total for every entity. That's exactly the "same value on several radars" symptom, and those are the measures where TREATAS (or a shared dimension) is needed.

The entity used in the slicer, the visual filter or the Deneb field well comes from a table other than t_pbi_dash.

The relationship between t_pbi_sequence_date and t_pbi_dash. If one exists, its filter combines with your date-overlap condition. If the date table is meant only to define the period, keep it disconnected.

To diagnose quickly, build a matrix with entite_realisatrice on rows and t_radar_Indicateurs[Indicateur] on columns, use your Radar Value measure, and add these two debug measures:

Debug Entity = CONCATENATEX ( VALUES ( 't_pbi_dash'[entite_realisatrice] ), 't_pbi_dash'[entite_realisatrice], ", " ) Debug Rows = COUNTROWS ( 't_pbi_dash' )

Any column of the matrix that shows the same value on every row is a measure that isn't responding to the entity.

Long-term: create an Entite dimension table related one-to-many to t_pbi_dash and to every other fact table, and use its column in the visuals and slicers. You can then drop TREATAS completely.

Deneb

Once the DAX is fixed, Vega only needs to display the decimal value it receives, with no conditional ×100 conversion:

Use "domain": [0, 1] with "clamp": true.

Format the labels with format(datum.Taux, '.1%').

If you'd rather show an overshoot above 100% than cut it off, set the domain maximum to max(1, the largest value in the data) instead of clamping.

I agree with you that Vega isn't the source of either issue. Fixing the DAX scope and checking the measures in a normal table first is the right order.

BBF

Forum Discussion

Deneb/vega on power bi

Related content

Deneb Vega Lite Sub Columns?

Deneb - Vega Lite Small Multiples

¿Sub columnas Deneb Vega Lite?

Deneb is fantastic! Advanced examples of Vega visuals

Deneb Vega-Lite use expression for scale domain?